Advanced Excel: Decision Making Functions

Workbook in Excel 95

If-Then

The If-Then function consists of three parts: a condition, what to do when the condition is true, what to do when the condition is false. The structure of the function is as follows:

=IF(condition,showwhentrue,showwhenfalse).

Note that the three parts are separated by commas.

The Condition

Basic Operators

The condition typically consists of three parts: a Excel cell address, a conditional operator, and a value. 

To test whether the value in cell A2 is greater than 400 we would use the conditional expression =A2>400.  The first equals sign is NOT part of the conditional expression.  All functions and expressions in Excel start with an equals sign. In the above case =A2>400 is true and the cell should display TRUE.  Try other values in A2 and see what results.  Conditional expressions produce a result of TRUE or FALSE.  The conditional operators include

= equals
> greater than
< less than
>= greater than or equal to
<= less than or equal to

The value can be a number or a word. 

• If the value is a number then the number can be typed as is: =A2<=500
• If the value is a word or combination of letters and numbers, then the value must be enclosed in quotes: =A2="Maintenance"  If words or letters are not in quotes then Excel thinks you are trying to type a cell address and Excel gets confused.  

Try typing in =A2>550 into cell B2.  Do not forget the lead equals sign. Press enter. What is the result in B2? Try changing the number in A2 to something larger than 550.  What happens?

Logical Operators

If you have more than one condition you need to meet, then you need logical operators such as AND and OR.  AND and OR consist of lists of conditional expressions using the basic operators separated by commas.

For our College entrance test all three scores must each be above 400. Given the following spreadsheet:

the cell D2 could contain =AND(A2>=400,B2>=400,C2>=400).  Vary the value of A2, B2, and C2 to test this function.

AND is true if all of the conditions are true.  OR is true if at least one condition is true. 

For those who wish to construct boolean algebra tables there is also a NOT function available.  Using NOT, AND, and OR other functions such as NAND (hot and) and XOR (exclusive or: either one or the other but not both).

Show  When True/ Show When False

These parts of the If-then function can be numbers, words, or combinations of letters and numbers.  For the later two instances quotes must be used.  Try typing =IF(A2>=400,"National","State") into cell D2.  Be careful to enter all quotes and commas!

Then experiment with the value in A2.

Nesting Functions

The logical functions and any other function can be nested, put inside of, the if condition in the conditional expression location.  Although typing these correctly can become a challenge, the result can be a tremendously powerful expression.   Remember that these can be filled-down to make hundreds of functional operations on hundreds of rows.

Nested If-Then function

The If-Then function itself can also be nested!  This allows you to create multi-way decisions. Keeping track of the logic along with all the parentheses and commas makes these nested if-then functions difficult.  I can only suggest you play with the following nested If-Then and see how it works.  I have tried to simplify the situation some by adding an average column and substituting >= with >

Now we can make three-way, four-way, and multi-way decisions.  We can nest ever more If-Then functions to make multi-way decisions. I use the following to determine the grade for a student where M2 contains their score:

=IF(M2<60,0,IF(M2<70,1,IF(M2<80,2,IF(M2<90,3,4))))

If I am using percentages I use:

=IF(M2<0.60,0,IF(M2<0.70,1,IF(M2<0.80,2,IF(M2<0.90,3,4))))

There is a rounding error problem: A 59.8 percent displays as "60%" in my grade book, but the above function gives the student a 0 or an "F."  I solve this by using:

=IF(M2<0.595,0,IF(M2<0.695,1,IF(M2<0.795,2,IF(M2<0.895,3,4))))

Using the CHOOSE function I convert the number to a letter grade (A=4, F=0).

Of course if you want to do the entrance scores closer to correct according to college policy, then you need to use the AND function.  The result is seen below.

Of course the actual policy is two columns over 470 puts you in the national campus.   This means nesting an OR function and testing all possible two column combinations (three OR functions nested inside an AND function).  This gets terribly complex to look at.  Call me when you need to build such nasty beasts.

Choose

The choose function selects the nth item from a list.

The syntax for choose is =CHOOSE(n,firstItem,secondItem,thirdItem,fourthItem,…) where n must be a cell address that contains an integer. The Nth item is chosen by the integer.

This function is essentially how I convert from a GPA to a letter grade.  I should note that because I use 0 for an F, I have to actually use the following function:

The A2+1 adds one to A2 to change from a range of 0-4 to 1-5.

This can be a fun function. To learn what day you were born on, you could use the following function.  Note that this is a nested function which nests the function WEEKDAY into the first element of the CHOOSE function.

CHOOSE can be nested into an IF-then function.  Using decision functions along with other Excel functions can produce some fairly complex outputs. 

For example, to determine one's African day name in southern Ghana one needs to determine the sex of the person and the day of the week on which they were born.  This is an example that combines a nesting CHOOSE functions inside of an IF-then function.   I would not recommend trying to type this devilishly long string, it is meant as an example of where you can go with these functions.

Workbook in Excel 95

The CHOOSE function is limited to 29 choices and can not be longer than 255 characters. The CHOOSE function only operates on integer choices. When the number of choices is more than 29 or when working with real numbers including decimal points the VLOOKUP function is necessary.

VLOOKUP

The Vertical Lookup function is another way to tackle making multiple choices based on the data in a column.  I first used this function in 1987 to perform "look ups" to a table of tax depreciation percentages using the 1986 U.S. tax code.  I think I have used this function only two or three times since that time.  In other words the function is not one I find daily use for, but when you need this function you really need it. 

The function has three parts: the value in the list to be tested, the range of the look-up table in absolute addressing mode, and the number of the column to reference.   This function can do some fancy stuff.  Below I use a simple example.   You do not need to keep typing the function: it will "fill down" correctly as long as the look-up table addresses are absolute addresses.

The function checks the value in the list to be tested, looks down the first column of the  look-up table range for a match, and then returns the value in the nth column of the look-up table.  If there is no match, then it uses the largest value that is less than or equal to the look-up value.  If the look-up value is smaller than the smallest value in the first column of look-up table, VLOOKUP returns the #N/A error value.   Got that?  So you can do tax table, etc.

Thus VLOOKUP can operate on numbers, or, as above, on text.  In the above case it is overkill to use VLOOKUP, but again, if the value list is 2000 items long, then a VLOOKUP table can save your fingers a lot of typing.

In the example below VLOOKUP calculates the remaining value of various assets of various ages.  The remaining value is set by the table seen on the right.  The two formulas are filled-down and display the required results.