The 2 Most Important
Lesson 6: Excel Spreadsheets Logical Functions (7)
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.
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=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, 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(OR(A1=1,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:
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.
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:
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 you want to know what accounts will be overdue in 30 days you will write:
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:
You are a teacher and you want to transform numerical grades into letter grades, here is the formula:
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, MOD, TRUNC and &
How many dozens are there in 106 units?
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:
IF, MOD, TRUNC and &
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:
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:
We fine tuned:
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.
SUMPRODUCT ¦ INDEX/MATCH ¦ SUBTOTAL ¦ ISERROR ¦ HLOOKUP ¦ LOOKUP ¦ VLOOKUP
AVERAGEIF ¦ AVERAGEIFS ¦ COUNTIF ¦ COUNTIFS ¦ SUMIF ¦ SUMIFS ¦ IFERROR