Functions, some of the most useful tools in Excel, let you perform a variety of tasks automatically within a spreadsheet. They return different results depending on certain circumstances, such as whether a particular condition is true or false.
Excel includes a number of these functions that you can use to not only to make working with spreadsheets faster, but also to help ensure your accuracy. These functions let you code the decision-making process inside your worksheets rather than relying on the person who enters the data to make the choice and enter the result.
To see how a decision-making function can work, consider a situation where you charge a flat fee for shipping up to a certain value of invoice and a percentage of the invoice value above that. Instead of manually checking to see whether a flat fee or percentage value is appropriate and then entering the amount into a cell, you can have Excel do the work for you.
Using a function you can point Excel at the invoice total and it will determine whether to use the flat fee or calculate a percentage value. Later, if the invoice total changes, the function will recalculate and the shipping cost will be reassessed based on the new information.
There are a number of Excel formulas that work in a similar way by allowing you to create one function to handle multiple situations. In this article we will look at three of them, the IF function, the CHOOSE function and the VLOOKUP function.
Figure 1: This IF function calculates shipping for our shipping costs scenario.
(Click for larger image).
The IF function is useful in the scenario mentioned earlier. Let’s suppose we have a situation where the cost of shipping a product is $4.95 for any amount of product up to and including $20.00 in value and then 25 percent of the invoice price over that amount. You can construct the IF function in such a way that it tests the amount of the invoice and determines if it is $20.00 or less. If it is, the flat fee goes in the cell, and if not a percentage value goes in. The IF function does the calculation for you, assuming the invoice total is located in cell G15, as in the example shown in Figure 1.
The function looks like this: =IF(G15<=20,4.95,G15*0.25)
The syntax of the function reads: =IF(test, calculation or value if true, calculation or value if false)
There is one thing to be aware of when you use an IF function and that is that it will only operate if you have a test that can be answered either True or False, or Yes or No. In simple terms, this means that Excel can answer the question “Is it raining outside?” because the answer is either True or False, Yes or No. However, Excel cannot answer the question “What is the weather like outside?” because this cannot be answered as True or False, Yes or No. In our scenario, the question “Is the invoice total $20 or less?” can be answered Yes or No.
IF Function with Multiple Tests
Figure 2: This more complex IF function uses an OR operator to make two tests that determine shipping cost.
(Click for larger image).
It’s possible to specify multiple tests for an IF statement. For example, consider in our previous example that there is an exception to the shipping cost calculation in the case of shipping to California – California shipping is always $4.95, regardless of invoice cost.
In this situation, we need to check not only the invoice amount but also whether the parcel is being shipped to California. This IF function will make the calculation assuming that a two digit state abbreviation is located in cell G7:
The function looks like this: =IF(OR(G7=”CA”,G15<=20),4.95,G15*0.25)
This function says: if the item is being shipped to California or if the invoice total is $20.00 or less, then the cost is $4.95 otherwise it is 25 percent of the invoice total. In this case we have used the OR operator to manage the test because either or both the tests must be true. In other circumstances you may have a need for a test where both situations must be true and in that situation you would replace the OR operator with the AND operator.