The 2 Most Important

Excel Functions

AND
Formulas

SUMPRODUCT

AND

INDEX/MATCH

Lesson 6: Excel Spreadsheets Logical Functions (7)

In annex 6 you have found a description of all 7 Excel functions in the "Logical" category. Below is the list of the 4 most useful ones. See more on IFERROR in lesson 25

Tips

You cannot use an IF formula to change the color of the font or of the cell's background based on a value (criteria). To do so you will use " Conditional Formatting" .

When you develop a condition for an IF formula it is not case sensitive.

The basic IF formula looks like this =IF(condition,value if condition is True,value if condition is False). So =IF(A1=1,9,8) in plain English means if the value of cell A1 is 1 the value in which this formulas resides is 9 otherwise it is 8.

Remember that you cannot nest more than 7 IFs within the same formula. Nested IFs are IFs within IFs like in =IF(A1> =90,"A" ,IF(A1> =80,"B" ,"C"). In other words if a condition is true you want to test another condition in such cases we are talking about nested IFs. In plain English this formula says: if the value of cell A1 is equal or higher than 90 the result in the cell where this formula resides is the capital letter "A" , if the value in cell A1 is 80 or greater then the result is "B" else the result is "C" . Below you will see a way to go around this limit.

Examples of Basic Excel Logical Formulas

In this section I can only give you examples of simple IF formula because OR and AND are not used by themselves.

IF

The basic IF formula looks like this =IF(A1=100,9,8). In plain English it means if the value of cell A1 is 100 the value in which this formulas resides is 9 otherwise it is 8.

You can also write =IF(A1< > 100,9,8). In plain English it means if the value of cell A1 is DIFFERENT than  100 the value in which this formulas resides is 9 otherwise it is 8. Using the " smaller than sign" (< ) and the " greater than sign" (> ) means different than.

You can also write =IF(A1=> 100,9,8). In plain English it means if the value of cell A1 is equal to or greater than 100 the value in which this formulas resides is 9 otherwise it is 8. Remember the order: the equal sign is first.

When you use text value you nee to use the double quotes.
You will  write =IF(A1="Peter" ,9,8). In plain English it means if the value of cell A1 is Peter then the value in which this formulas resides is 9 otherwise it is 8.

You will  write =IF(A1=1," Peter" ,8). In plain English it means if the value of cell A1 is equal to 1  then the value in which this formulas resides is Peter otherwise it is 8.

Finally if you want to say that if the value of cell A1 is equal to 1 the result should be an empty cell or a space you will write:
=IF(A1=1," " ,8) for the space (notice the space between the double quotes
=IF(A1=1,,8)  or =IF(A1=1," " ,8) for the empty cell. It is either nothing between the two commas or a set of double quotes with nothing in between.

IF, AND and OR

You may set more than one condition and link them with AND or OR. You write  all the conditions separated by commas within a set of parentheses.

Using AND or OR is easy it is the logic that in sometimes mind boggling.   For example
=IF(AND(A1=1,A1< > 2),9,8)
means that if the value of cell A1 is equal to 1 and  different than 2  return 9 else return 8. Now remember that for the formula to return a 9 the value in cell A1 MUST respect BOTH conditions. All numbers are different than 2 including 1 but only 1 respects both conditions so the formula could simply be:
=IF(A1=1,9,8)

=IF(OR(A1=1,A1<> 2), 9,8)
means that  if the value of cell A1 is equal to 1 or different than 2  return 9 else return 8.  In this case a 9 is returned for any value that respects ONE OF THE conditions. The number 1 respects both  conditions and all other numbers except 2 respect at least one condition so again this formula could simply be:
=IF(A1<> 2,9,8)

IF, AND or  OR?

Let's say that you want to give a B to a student whose grades are between 75 and 85. Should you write:
=IF(OR(A1=< 85,A1=> 75),"B" ," ")
or
=IF(AND(A1=< 85,A1=> 75),"B" ," ")

Let's look at the first formula. Any number that respects ANY of the two conditions will result B. 95 is good because it is larger than 75. 78 is good because it respects both conditions. 35 is also good because it is smaller than 85. So this formula is wrong.

Only the numbers between and including 75 and 85 respect BOTH conditions and will result in a B. All the other numbers only respect ONE of the conditions and are excluded. So the second formula is the right one.

I have been working with numbers for more than 30 years now and I still doubt my own logic. My advice is TEST YOUR LOGIC FORMULAS.

IF (Nested)

You will not be confronted with this situation often but let's illustrate the solution to the limit of 7 nested IFs. You will need as many formulas as you have groups of 6 conditions.   For example suppose you want to replace numbers by letters 1=A, 2=B and so on and the number submitted is in cell A1. For values of A1 from 1 to 12   you will need 3 formulas in 3 different cells. The formula in B1 will be:
=IF(A1=1,"A" ,IF(A1=2,"B" ,IF(A1=3,"C" ,IF(A1=4,"D" ,IF(A1=5,"E" ,IF(A1=6,"F" ," " ))))))
notice that if the value of cell A1 is larger than 6 the result is an empty cell. Notice that there are the same number of closing parentheses as of opening parentheses.
the formula in C1 will be:
=IF(A1=7,"G" ,IF(A1=8,"H" ,IF(A1=9,"I" ,IF(A1=10,"J" ,IF(A1=11,"K" ,IF(A1=12,"L" ," " ))))))
and the formula in C1 to show the final result will be a concatenation of the results in B1 and C1
=B1 & C1
Hide columns B and C.

IF, NOW

You have a list of receivables with the date due in column " D" , the following formula in column E will show " Overdue" if the date in column C is earlier than today and will show nothing if the date is later.
=IF(D1< NOW(),"Overdue" ,"" )
In plain English: if the date in C1 is earlier than today (NOW() in Excel language) then write " Overdue" if not do not write anything (" " in Excel language).

If you want to know what accounts will be overdue in 30 days you will write:
=IF(D1< NOW()+30,"Overdue" ,"" )

And if you want to see what accounts are overdue based on a date in cell G2 for example you will use this very simple:
=IF(D1< G2,"Overdue" ,"" )

IF

You are a teacher and you want to transform numerical grades into letter grades, here is the formula:
=IF(A1> =90,"A" ,IF(A1> =80,"B" ,IF(A1> =70,"C" ,IF(A1> =60,"D" ,"E" ))))

IF, ISNUMBER, LEFT  and MID

In UK all postal codes start by a prefix of one or two letters. My correspondent wanted a formula to extract the prefixes so he could make a list of them. With the postal codes in column one the following formula in column 2 would do the job.
=IF(ISNUMBER(MID(A1,2,1)*1),LEFT(A1,1),LEFT(A1,2))
Depending on the number of characters in the prefix the formula should return the first  character from the left or the first 2  characters from the left: LEFT(A1,1) or LEFT(A1,2)
Before any of these solutions is applied we must check if there are one or two letters at the beginning of the postal code. To do so we will check if the second character MID(A1,2,1)  is a number. The problem here is that any character from a text string is consider as a letter by Excel. Postal codes, serial numbers and others that include a letter or are formatted as text  are text by nature . So we multiply the second character by 1. If the character is a digit  to begin with it becomes a number but if it is a letter it doesn't: ISNUMBER(MID(A1,2,1)*1).

IF, MOD, TRUNC and &

How many dozens are there in 106 units?
With the number of units in cell A1 the formulas in B1:
=TRUNC(A1/12,0) will return the number of   complete dozens
this formula in C1:
=MOD(A1,12) will return the number of units left when the total number is divided by 12. 

If you want to present the result as " 8 dozens and 10  units" in a single cell you will use the following formula combining math & Trig functions and the ampersand (& ) sign:
=TRUNC(A1/12) & " dozens and " & MOD(A1,12) & " units"
But what if there are 96 units and you don't want the result to show as "8 dozens and 0  units" but as "8 dozens" . You will then use this formula:
=IF(MOD(A1,12)=0,TRUNC(A1/12) & " dozens" ,TRUNC(A1/12) & " dozens and " & MOD(A1 12) & " units" )

IF, MOD, TRUNC and &
How many dozens are there in 106 units?
With the number of units in cell A1 the formulas in B1:
=TRUNC(A1/12,0) will return the number of   complete dozens
this formula in C1:
=MOD(A1,12) will return the number of units left when the total number is divided by 12. 

If you want to present the result as "8 dozens and 10  units" in a single cell you will use the following formula combining math & Trig functions and the ampersand (& ) sign:
=TRUNC(A1/12) & " dozens and " & MOD(A1,12) & " units"
But what if there are 96 units and you don't want the result to show as " 8 dozens and 0  units" but as " 8 dozens" . You will then use this formula:
=IF(MOD(A1,12)=0,TRUNC(A1/12) & " dozens" ,TRUNC(A1/12) & " dozens and " & MOD(A1 12) & " units" )

DATEDIF, NOW, AND and IF

My client wanted a spreadsheet that would tell her when it is the birthday of an employee. We created a spreadsheet with the names in column A and the dates of birth in column B. In column D was this formula =NOW() that changes date each day. In column C  we put this formula:
=IF(AND(MONTH(B1)-MONTH(D1)=0,DAY(B1)-DAY(D1)=0),"Anniversary","")

We fine tuned:
=IF(AND(MONTH(B1)-MONTH(D1)=0,DAY(B1)-DAY(D1)=0),"Happy Anniversary" ,IF(AND(MONTH(B1)-MONTH(D1)=0,DAY(B1)-DAY(D1)>0,DAY(B1)-DAY(D1)<7)," Anniversary coming" ,"" ))

Copy/paste the formula above in your spreadsheet. If you want to be alerted more that a week before the anniversary change the 7 for 30 in the formula. This way you will be alerted a month in advance.