The lession is availble at the web site below downloadable in Word
Conditional Logic Statements in ExcelIt is impossible to make much progress in Microsoft Excel without understanding IF statements. Imagine you want two different outcomes to a calculation dependent on whether a condition was met or not.
The simplest starting point would be to assume that A1 of the spreadsheet contains the heading Name, B1 Salary, C1 Area, D1 Grade, E1 absence. In the second row of the spreadsheet the corresponding cells below contain the text John, 150,000, New York, Director and 0 respectively. Assume for the purpose of this article that case is not significant.
If in the cell F1 if the following calculation were typed
=IF(B2>50000,”RICH”,”POOR”)
Then John would join the rich club. Note the quotation marks since the true and false outcomes are text. This is called a “simple if” since there are two possible outcomes.
In f1 type in Donations. Let us assume for now that that the rich can afford to pay more than the poor. In G2 type
=IF(C2=”RICH”,B2*10%,20)
This calculation assumes that the rich are happy to pay 10% of their income (a calculation) whilst the poor just pay 20 (a value).
It’s still a simple if because there are still just two outcomes based upon a single condition. The outcomes can either be a constant or a calculation.
The condition can be more complex. Imagine that there were two possible moods Happy or unhappy but there are three requirements to make you happy.
1. Money more than 100,000
2. Location needs to be “new York”
3. grade has to be “director”
The function to achieve this is =And(condtion1, condition2, condition3) potentially up to 30 conditions. All condition must be true for the function to return TRUE otherwise it returns FALSE.
=AND(B2>100000,D2=”NEW YORK”,C2=”DIRECTOR”)
In this instance the function returns TRUE because indeed you are earning the right salary, you are in New York and you are a director.
If =Or were substituted for =AND only one condition would need to be true before the function would return TRUE
=OR(B2>100000,D2=”NEW YORK”,C2=”DIRECTOR”)
However, TRUE and FALSE is not particularly friendly as the outcome to the calculation. AN improvement would be to nest (enclose) the =AND in a conditional logic function
=if(and(b2>100000,d2=”new york”,c2=”director”),”happy”,”not happy”)
Imagine you are in New York and you get demoted down to manager the function as it currently stands would say you are unhappy but after drowning your sorrows with a few Long Island Ice teas you decide that since you still have the money and the location you are still happy.
What could we do ?
Why not nest a or statement inside the and
=IF(AND(B2>100000,D2=”NEW YORK”,OR(C2=”DIRECTOR”,C2=”MANAGER”)),”HAPPY”,”NOT HAPPY”)
In G1 type in Swimming and H2 type in heated assuming you like to swim in a heated swimming pool. I don’ t know about you but the prospect of swimming in the river would not make me very happy.
How could we adjust the calculation
=IF(AND(B2>100000,D2=”NEW YORK”,OR(C2=”DIRECTOR”,C2=”MANAGER”),NOT(H2=”RIVER”)),”HAPPY”,”NOT HAPPY”)
In other words in order to be happy you must earn 100,000, be in New York, as either a Director or a manager but not swimming in the river.
However, even this is too simplistic . What if you were capable of more than two moods based on your salary. It is possible to nest IF statements inside each other.
=IF(B2>50000,IF(B2>100000,”HAPPY”,”CONTENT”),”UNHAPPY”)
The limit in Excel is seven condition eight possible outcomes.
This only leaves one other level of complexity. What if you had more than eight possible moods ?
This forms part of the next lessonon Using Lookup and reference functions in Microsoft Excel
