Working with Functions in Excel

Monday Apr 21st 2008 by Helen Bradley
Share:

Improve efficiency and accuracy in your spreadsheets by using Excel's function tools. Helen Bradley shows you how.

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.

IF Function

Excel IF function screen shot
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

Excel IF/OR function screen shot
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.

Continue to Page 2

CHOOSE Function

The CHOOSE function is an interesting function that lets you select an item from a list of items. One simple way to see it at work is to use it to convert a date to a day of the week -- something that Excel can't do.

When you use the WEEKDAY function on a date in Excel, it returns a number representing the day of the week in the range from one to seven representing the days Sunday through to Saturday. The problem with this is that most times, instead of a day number, you'd really prefer to see a day name. In this case, you can combine the CHOOSE function with the WEEKDAY function to convert the number into a day name.

This function applied to a date in cell A1 will return the corresponding day of the week it represents:

The function looks like this: =CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Excel CHOOSE function screen shot
Figure 3: The CHOOSE function can be used to return the name of a day from a date.
(Click for larger image)
.

In this case the CHOOSE function takes the day number that the WEEKDAY function returns and turns it into the corresponding day of the week. The first variable entry in the function is the number to use and, following this are the series of values that the function will return depending on the first variable value. The CHOOSE function can return up to 29 different values.

VLOOKUP

One of the most useful functions, VLOOKUP lets you look up a value in the first column of a table and to return data from the same row of the table but a different column.

For example, consider a situation where you have a table of data containing currency conversion rates for a series of different currencies. Instead of having to scroll through the table looking for the required conversion rate every time you need to calculate a conversion, you can have the lookup formula do the work for you.

The best way to approach this task is to select all the data in your table and name that range by choosing Insert > Name > Define and type a name for it. This lets you use the table name in your lookup function rather than having to remember the range details each time you need to use it. This step isn't required but it will make your work easier.

Excel NAME function screen shot
Figure 4: Naming a data table makes creating a lookup table easier.
(Click for larger image)
.

You can now look up a value stored in a cell in the first column of the table and return data from a different column. Table columns are numbered with 1 being the leftmost table column and then 2, 3 etc., as you move to the right.

If your table is called CurrencyConversions, and if you're looking up a conversion rate for a currency named in cell C12 and converting it into the currency listed in the 3rd column of the table, this function will do the work:

The function looks like this: =VLOOKUP(C12,CurrencyConversions,3)

In the example worksheet in Figure 5, this formula will make a conversion using the currencies entered in cells C12 and F12 and the amount in cell B12:

The function looks like this: =VLOOKUP(C12,CurrencyConversions,IF(F12="AUD",2,IF(F12="CAD",3,(IF(F12="EUR",4,IF(F12="GBP",5,6))))))*B12.

Excel VLOOKUP function screen shot
Figure 5: This VLOOKUP function, combined with some nested IF functions, makes a currency conversion.
(Click for larger image)
.

Notice here that a series of IF functions have been nested inside each other to convert the currency name from cell F12 into a column number so we can extract the conversion rate from the table.

When using the VLOOKUP function, you should note that it is important that the values in the first column of the table are sorted in ascending order for it to work.

I've given you a brief introduction to some possible applications for the functions IF, CHOOSE and VLOOKUP. You will find that once you see their potential, you have lots of other ways you can apply these functions to your work every day.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!

Return to Page 1

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved