Functions Making Decisions in Spreadsheets

Robert P. Webber, Scott McElfresh, Longwood University
 

Spreadsheets have functions that allow varying calculations to happen.   for instance,  in January, you are unlikely to want to calculate the heat index, but you may with to calculate a wind chill.     Most computer programming, including spreadsheets relies on something called a Boolean, named after a mathematician named George Boole.    Technically, a Boolean is something that evaluates to either  TRUE or FALSE.     We might view it as an answer to a yes-or-no question, with no ambiguities.    FactCheck.org views it as a judgement about a particular statement.

Generally, when involving decisions, computer programming involves separating the possibilities into two paths, and a question that can be asked to determine which path to take.    

For instance, someone may say "I am going to go buy potato chips.  If Walmart has bags over 72 ounces, I will buy them there.  If not, I will go buy chips at Food Lion."       There is a clear situation that will determine this person's course of action.    Somehow they need to ascertain whether Walmart sells bags of chips over 72 ounces.   He/she may do this by calling and asking, or going there and finding out themselves, or asking a friend who has been there recently.      In any case, this person will then know whether to buy at Walmart or at Food Lion.


The question being asked must have a clear answer.    For instance, in English, we might say "If it is supposed to rain today, I will take an umbrella."    Often implied with this is that the person will NOT take an umbrella if the forecast calls for sun.  The "question" in this case:  "Is it supposed to rain today?"     We can answer this question by looking online for a weather forecast.   However, if the forecast calls for a 50% chance of rain, what do you do?    It must be assumed that there is a threshhold at which the person says "Yes, it is supposed to rain today."      In the computer, there is no room for uncertainty.   It is either true or false.

IF()    The  IF()  function tests a condition and returns one of two values, depending on the result of the test.  The condition must evaluate to true or false.  The  IF()  statement takes three arguments.  Its general form is

            IF(condition, value_if_true, value_if_false)

 

If the condition is true, the statement displays the first value.  If the condition is false, it displays the second value. 

Side note:  The third argument may be omitted.  If so, the statement returns  FALSE  if the condition is false.

 

A sample use of  IF()  is to prevent division by zero.  Consider the formula

            =A3 / A4

If cell  A4  contains  0, the formula results in a error, because division by  0  is undefined.  Instead, write

            IF (A4 <> 0, A3 / A4, “Error”) .

Now, if  A4  does not contain  0, the division is done.  (Notice that  <>  is the abbreviation for unequal.)   If it is  0, the division is skipped, and the word  Error  is displayed.

 

Example:   Consider the scenario of a  vendor near an performance stage.      Below are 4 upcoming shows with the number of tickets sold and the length (in minutes) of each show.

	A	B	C
1	Tickets	Show Length
2	500	60
3	650	75
4	425	50
5	300	80

Past experience shows that if a show is longer than an hour, the vendor will sell an average of $5 of food per ticket sold, but if the show is not longer than an hour, the vendor will only sell an average of  $3 of food per ticket.     We can have the spreadsheet help us figure out how much income to predict.

In Cell C2, type   = IF( B2> 60,   5* A2,    3*A2)   and fill down to cell C5.        The result:

	A	B	C
1	Tickets	Show Length	Anticipated income
2	500	60	1500
3	650	75	3250
4	425	50	1275
5	300	80	1500

 

ISNUMBER()    This function returns  TRUE  if the argument is a number and  FALSE  otherwise.  For instance,  ISNUMBER(A1)  returns  TRUE  if cell  A1  contains the number  14, say, and false if it contains the word  “fourteen.”

 

Now suppose a spreadsheet contains a list of employees and their annual salaries.  Employee names are in column A, last year’s salaries in column C, and this year’s salaries in column D.  However, some of the employees are new, and their old salary fields are blank.  Here is the data.

Name		Old salary	New salary
Aaron, Ann		25000	26200
Bolding, Bill			30000
Brown, Buddy		45100	46000
Dodge, Jane			36000
Emerson, Kim		38200	39200
Etheridge, Alice		40000	40000
Falconer, Faye		32000	34500
Godwyn, Gregory		41000	42000

 

 

The problem is to calculate the percent of increase in salary for the continuing employees and put the results in column E.  Leave the cell in column E blank if the employee is new.

To calculate the percent of increase,

• Find the increase by subtracting the old salary from the new:  increase = new_salary – old_salary.
• Divide the difference by the old salary:  increase / old_salary.
• Convert to a percent by multiplying by  100 .

For example, consider Ann Aaron’s figures. 

            Increase = 26200 – 25000 = 1200 ,

            Proportion of increase = 1200 / 25000 = 0.048 ,

            Percent of increase = 0.048 * 100 = 4.8 .

 

We can combine these into one line.

            Percent of increase = (25200 – 25000) / 25000 * 100

 

Using cell references, Ann’s percent of increase  is  (D3-C3) / C3 * 100 .  However, we should do this calculation only if cell  C3 (the old salary) actually contains a number. 

Guard the calculation with an  IF().  Put  ISNUMBER(C3)  as the condition, the formula for calculating the percent increase in the second argument, and “”  in the third.  If  C3  contains a number, the calculation will be done, and the result displayed.  Otherwise, a blank will be displayed.  That is, put the following formula in cell E3.

 

            =IF( ISNUMBER(C3), (D3-C3)/C3*100, “ “)

 

Then click and drag this formula down column E.  Here are the results.

 

Name		Old salary	New salary	Percent increase
Aaron, Ann		25000	26200	4.8
Bolding, Bill			30000
Brown, Buddy		45100	46000	1.99556541
Dodge, Jane			36000
Emerson, Kim		38200	39200	2.617801047
Etheridge, Alice		40000	40000	0
Falconer, Faye		32000	34500	7.8125
Godwyn, Gregory		41000	42000	2.43902439

 

More complicated questions

There are times when the situation calls for a more complicated question.    Some examples:

• "I will be very happy tonight only if the cafeteria has brownies, and my favorite act wins America's Got Talent".  

In this case, there are 2 situations that must occur for this person to be happy.    The word "and" says that BOTH must be true.   If the cafeteria has brownies, but their favorite act loses, they will be unhappy.    

• "Tomorrow, I will go for a run if I wake up before 7 am and it is not raining." 

Again, two conditions must be satisifed.   Due to the word "and", it is assumed that if the person wakes up late, then they will not go for a run.  Additionally, if it is raining, they will not go for a run.


AND()    The AND function allows us to see if all specified conditions are met.    Consider the following sheet, where each row represents a coupon.  Column A is the type of product, and Column B is the discount amount for the coupon.

	A	B	C	D
1	Store	.75
2	Name	1.25
3	Name	.50
4	Store	1.50
5	Store	1.00
6	Name	1.00

Imagine at this store, coupons are doubled for all Name brands, but not for coupons over $1.    
Type into cell C1  =AND(A1="Name" , B1 <=1) and fill down to cell C6.  The result will be:

	A	B	C	D
1	Store	.75	FALSE
2	Name	1.25	FALSE
3	Name	.50	TRUE
4	Store	1.50	FALSE
5	Store	1.00	FALSE
6	Name	1.00	TRUE

Now, in cell D1, type in  = IF ( AND(A1="Name" , B1 <=1),  B1 * 2,   B1) and fill down to cell D6 .  The result will be as follows, with column D indicating the actual savings from each coupon at this store.

	A	B	C	D
1	Store	.75	FALSE	.75
2	Name	1.25	FALSE	1.25
3	Name	.50	TRUE	1.00
4	Store	1.50	FALSE	1.50
5	Store	1.00	FALSE	1.00
6	Name	1.00	TRUE	2.00

OR()   The OR function allows us to see if at least one condition is met.       For instance, imagaine a climate-controlled building, where each room is its own "zone" with its own system of thermostat and sensors.    The policy is that the A/C will be on if the temperature is high or the humidity is high.    The temperature threshhold is 72, while the humidity threshhold is 60.

Consider the following small spreadsheet indicating the information on 5 rooms in the building.

	A	B	C	D
1	Room #	Temperature	Humidity
2	100	70	50
3	105	70	62
4	200	73	55
5	205	70	55
6	210	75	65

 

In cell D2, we can put   = IF( OR(B2 > 72, C2 > 6),  "ON", "OFF")    and fill down to D6.    The result would be:

	A	B	C	D
1	Room #	Temperature	Humidity
2	100	70	50	OFF
3	105	70	62	ON
4	200	73	55	ON
5	205	70	55	OFF
6	210	75	65	ON

 

For rooms 100 and 205, neither condition is met, so the AC is off.

For rooms 105 and 200, one of the two conditions is met, so the AC is on.

For room 210, the temperature is above threshhold and the humidity level is also above threshhold.    This is okay, because AT LEAST ONE is true.

Credits and licensing

This article is by Robert P. Webber and Scott McElfresh, licensed under a Creative Commons BY-SA 3.0 license.

Version 2015-Sept-06 12:00