Working with Functions in Excel - Page 2

By Helen Bradley | Posted April 21, 2008
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



Page 2 of 2

Previous Page
1 2
 

Comment and Contribute


     

    Get free tips, news and advice on how to make technology work harder for your business.

    Submit
    Learn more
     
    You have successfuly registered to
    Enterprise Apps Daily Newsletter
    Thanks for your registration, follow us on our social networks to keep up-to-date