Logical Operators

I've been learning about Advanced Excel formulas and functions.

Today, I covered Logical operators in the following areas:

• IF statement

• Nested IF statements

• IFAND and IFOR operators

• IFNOT function

• IS statements

• IF Statement

I learned that this is used to test a condition and give a value if it's true or not.

For example, I have a list of numbers from 1 to 10, and I want to describe each value as big or small, big if it's greater than 5, and small if it's less than or equal to 5.

Now, for a small dataset like this, it's quite easy to fix the description manually, but most datasets are usually voluminous, making the manual method nearly impossible.

For this, we can use an IF statement. This is the breakdown of the function.

=IF(logical test, value if true, value if not)

For our list of numbers from 1 to 10, say the cell range is from D2 to D11, the statement would go like this:

=IF(D2>5,"Big","Small")

Note: The values, if true, and if not, are in quotes because they're texts. Also, “D2” refers to the cell position on the Excel worksheet.

Once you press Enter, it shows "Small" for one.

While you could retype the statement for the remaining values, that is tedious, especially when working with a large dataset.

You place your cursor on the small dark box on the bottom right of the cell, double-click it, and the other cells are filled.

1	=IF(D2>5,"BIG","SMALL")
2	SMALL
3	SMALL
4	SMALL
5	SMALL
6	BIG
7	BIG
8	BIG
9	BIG
10	BIG

• Nested IF statements

I learned this can be used when testing multiple conditions sequentially.

Say I have a list of scores between 40 and 100. I want to group them as 40–60 for Fail, 61–79 for Average, and 80–100 for Excellent.

Now, I have three groupings.

The If statement would be:

=IF(H4>=80,"Excellent",IF(H4<=60,"Fail","Average")), or it could also be: =IF(H4>79,"Excellent",IF(H4>60,"Average","Fail"))

40	=IF(H4>=80,"Excellent",IF(H4<=60,"Fail","Average"))	=IF(H4>79,"Excellent",IF(H4>60,"Average","Fail"))
55	Fail	Fail
60	Fail	Fail
70	Average	Average
80	Excellent	Excellent
90	Excellent	Excellent
100	Excellent	Excellent

• IFAND Statement

This is used when we want a result dependent on two conditions being met to get an accurate result. Using the above dataset, a scholarship will be awarded to females with excellent grades. Now, two criteria must be met. The statement would be written as:

Gender	Score	Grade	Scholarship
M	40	Fail	=IF(AND(I4="Excellent",G4="F"),"Yes","No")
F	55	Fail	No
M	60	Fail	No
F	10	Fail	No
F	80	Excellent	Yes
M	90	Excellent	No
F	100	Excellent	Yes

• IFOR statement

We use this when at least one of a set of conditions must be met.

This is a screenshot of the learning material. Here, the precipitation type has to be rain or snow for the condition to be wet. If neither, it is Dry.

• IFNOT statement

This checks for a situation when the logical operator isn’t true. It is written as:

We want to check the type of the condition; instead of using the IFOR statement with precipitation type as the criterion, we can use IFNOT with column C (precipitation amount) as the criterion. So, if it is not 0, the condition would be wet.

• IS Statement

ISBLANK, for example, would be written as:

=ISBLANK(cell reference)

Alright, that’s all for today. Thank you for reading to this point.

Let me know what you think. Cheers!!!