Chapter Six: Excel Functions

Chapter 6: Excel Functions. 1

Exercise 1: Boolean Formulas and IF Functions. 4

Exercise 2: Ajax Steak and Eggs Restaurant - Order Form.. 5

Exercise 3: Java Juice Delivery Form.. 6

 

Boolean Formulas

You can write formulas in Excel to compare values. If the values match the formula’s specifications, Excel displays “True.” If they don’t match, “False” is displayed.

Examples:      = A1 < 4,        = A1 = “Red”              = A1 < 10                   = A1 <> 5

These formulas will all evaluate as either True or False, depending on the contents of cell A1. Note that when you compare text strings, the string must be in quotes.

Boolean formulas are used as a test condition in IF Functions, as described below.

Functions

Excel has a built-in set of advanced formulas, called functions. Usually a function needs to be given one or more pieces of data (each piece is called an argument) before it will work. The arguments are listed inside parentheses following the name of the function. Each argument is separated by a comma.

Here are some commonly used functions:

SUM               Example = SUM(C3:G9)                 

                        A range is the required argument.

Result: Total of all numbers in the range.

AVERAGE    Example = AVERAGE(C3:G9)

                        A range is the required argument.

Result: Average of all numbers in the range.

ROUND         Example = ROUND(A4/3,2)

                        A formula is the first argument.

The second argument is the number of decimal places to display.

Result: if A4 = 5 then 5/3 = 1.6666 which is rounded to 1.67.

TODAY()        Example = TODAY()

                        This function does not need an argument.                                    

Result: The current date is displayed.

Selection Functions

Suppose you had a cell in the worksheet that could have more than one value, with the correct value depending on the contents of another cell. You can use a selection function to tell Excel which value to display. Some commonly used selection functions are IF, VLOOKUP, and HLOOKUP.

The IF function tells Excel which of two values to display in a cell.

The VLOOKUP and HLOOKUP functions find information in a table.

IF Functions

In Excel, an IF Function has three arguments.

The first argument is Boolean formula that compares two values.

The second and third arguments give two options of what to display in the cell, based on the result of the Boolean function. If the function evaluates as “True” the second argument option is displayed. If the equation evaluates as “False” the third argument is used.

Boolean

C3 is > 80

 

 

If True

 

Show PASS

 

If False

 

 

Show “FAIL”

If the value in cell C3 is greater than 80, then the condition is met. The word PASS will appear. If the value in cell C3 is NOT greater than 80 then the condition is not met.  The word FAIL will appear.

We would write the function like this: =IF(C3>80,”PASS”,”FAIL”) Commas separate each argument.

The two sides of the equation in the Boolean formula can be numbers, cell references, formulas, or functions. You can even use formulas that compare strings of text.

The second and third arguments in IF Functions can be

·        Text (called strings), always written between double quotes, or

·        Numbers, or

·        Formulas, or

·        Functions

You can even use an IF Function as an argument. This is called a nested IF.

Example: =IF(B9 = “Yes”, 21, IF(B10 = “Yes”, 22,0))

VLOOKUP Functions

VLOOKUP functions are used to find information from a table. For example, you could have a table showing the sales tax rates for different counties. You can give Excel the name of a county and it will show the tax rate for that county.

Sample: =VLOOKUP(C4,G5:H7,4)

The first argument is always a cell reference to the input cell.  The input cell stores a text string. Excel will look for a matching text string in the table. Only the row containing the matching text string will be used to generate data.

The second argument tells Excel the location of the table, using range notation or a range name. The range includes only the rows of data, not the column labels row. The text strings in the first column of the table must be sorted in alphabetical order.

The third argument, which is always a number, tells Excel that the information is found in the second or third or fourth (and so on) column of the table.

Data Validation and Lookup Functions

Excel uses text from an input cell to search a table for information. If a match for the text is found, Excel uses cells in that row of the table.

But what if the text in the input cell doesn’t match any of the text entries in the lookup table: then what happens? Excel displays the closest match, which is usually an incorrect result. Excel does not show any error message, so the spreadsheet users won’t know that anything is wrong.

To prevent this problem, you can put restrictions, called a validation list, on the input cell. Only text found in the validation list can be entered into the input cell.

To create a Validation List: Select the input cell. On the Data tab, in the Data Tools group, click the Data Validation icon. On the Settings tab, under Validation Criteria, choose List in the Allow box. You can type a list directly into the source box, using commas to separate each item in the list. Or you can click on the icon in the Source Box and then use the mouse to highlight the cells that contain your list. Press Enter and click OK.

Range Names 

You can assign a name to a group of cells by highlighting the cells with the mouse, then typing the name in the name box above cell A1. You can view range names by clicking the pull-down arrow in this box. Look on the Formulas tab, Defined Names group, for the Name Manager icon to find tools to create, edit and delete range names.

If the range has a name assigned to it you can use the name in a function or formula.

If the range E6:H8 is defined as “people” then the function =VLOOKUP(C4,E6:H8,2) could also be written as =VLOOKUP(C4,people,2). Range names do not have quotation marks around them when you use them in a formula or function.

The #N/A Error Message

This message appears in the output cell when the input cell in a VLOOKUP function is blank. You can use an IF Function to replace this error message with something else.

Example:  =IF(COUNTIF(J6,""),"Retry", VLOOKUP(J6,A28:F31,6))

In the example J6 is the input cell in the first argument of the VLOOKUP function.

The first argument tests to see if the input cell (J6 in this case) is blank.

If J6 is blank, Excel displays the content of the second argument. If J6 is not blank, Excel displays the result of the VLOOKUP function given in the third argument. 


Exercise 1: Boolean Formulas and IF Functions

Working with Boolean Formulas

Some formulas, called Booleans, do not produce numbers. Instead, they produce either a True or False response. Boolean formulas can be used to compare numbers or text strings. You can use cell references in Boolean formulas.

Enter the number 5 in cells A2 to A7. Enter the word “Tom” in cell A1.

In cell B1, enter = A1 > 5

In cell B2 enter = A2 >= 5

In cell B3 enter = A3 <5

In cell B4 enter = A4 <= 5

In cell B5 enter = A5 = 5

In cell B6 enter = A6 <> 5

In cell B7 enter = A1 = “Boy”

Create some Boolean Formulas

The speed limit is 65 MPH. If you are caught speeding, the fine is $280. Enter your speed in cell A10. Write a Boolean formula in cell B10 to test if you were speeding.

Your company pays a 2% commission to salespeople if they sell $20,000 or more of products in a single month. Enter the amount sold in cell A11. Enter a Boolean formula in cell B11 to test whether the sales equaled or exceeded $20,000.

Your company is considering projects to invest in. They will accept projects with a rate of return higher than 8%, and reject projects with a lower rate. Enter a rate of return in cell A12. Write a Boolean formula in cell B12 to test if the rate of return is high enough.

Working with IF Functions

IF functions have three arguments. The first argument is the Boolean formula. The second argument is what to display if the result of the Boolean formula is true. The third argument shows what to display if the result of the Boolean formula is false.

Create an IF function in cell C10 to show the fine. The first argument will be A10>65. The second argument will be 280. The third argument will be 0.

Create an IF function in cell C11 to show the commission. The first argument will be A11>=20000. The second argument will be A11*2%. The third argument is 0. You can use formulas as arguments in an IF function.

Create an IF function in cell C12 to show if the project is accepted or rejected. The first argument will be A12>8%. Use “Accept” and “Reject” as your other arguments. You can use text as arguments in IF functions, but the text must be included in quotation marks.

Change some numbers in column A to see how the results in column C change.


Exercise 2: Ajax Steak and Eggs Restaurant - Order Form

Download and save the file www.cob.sjsu.edu/bus91L/textbook2/BPTDATA.xls. Open the file. Select the Tables Worksheet.

Follow the instructions found on the worksheet.

Create

1.      Three Validation lists for the type of steak, type of omelet, and type of drink. These go in the second column of the form.

2.      Two Validation lists for the preparation options for eggs and for steaks.

Make up three options for each.

3.      Three VLOOKUP formulas showing the price of the selected food items.

Ajax Steak and Eggs  -  Order Form

Item

Type

Preparation

Price

Steaks

Validation List

Validation List

VLookup

Eggs

W/Hash Browns & Toast

Validation List

$3.95

Omelet

Validation List

 

VLookup

Beverage

Validation List

 

VLookup

 

 

Total

=SUM

Information needed for the Lookup Tables and Validation Lists:

Customers can order:

Steaks:                                                                                               Price  

            New York                                                                                $17.50

            Rib Eye                                                                                  $16.50

            Sirloin                                                                         $13.95

Preparation Options: Rare, Medium, Well-Done

Eggs, Hash Browns, and Toast:                                                      $3.95

            Preparation Options: Scrambled, Over Easy, Sunny Side-Up   

Omelets:                                 Ingredients                                         Price

            Cheese:                     Cheese                                              $4.45

            Cordon Bleu: Chicken, Ham, Swiss Cheese        $5.75

            Ham:                           Ham, Cheese                                    $4.95

            Western:                     Peppers, Onion, Ham, Cheese       $5.45

Beverages:                                                                                        Price

            Coffee:                                                                                   $ .99

            Juice:                                                                                      $1.19 

            Milk:                                                                                        $ .99


Exercise 3: Java Juice Delivery Form

Download and save the file www.cob.sjsu.edu/bus91L/textbook2/BPTDATA.xls. Open the file. Select the Delivery Form tab. Copy and paste the sheet into a new workbook.

Enter a function to automatically put a date into your invoice

In cell D7 enter this formula =TODAY() Make sure there are no spaces between the parentheses. This function automatically shows the current date.

Apply special number formatting

Select Cell B13 to format. On the number tab of the dialog box choose the Category “Special” and the Type “Phone Number.”

Create a validation list of county names in cell B12

Select cell B12. From the Data tab, Data Tools, select Data Validation. In the Settings tab set the “Allow” criteria to List. Click on the icon in the Source box. Use your mouse to highlight cells F8:F13. Click OK. Select a county.

Create multiple validation lists in cells B17-B25.

Select cells B17 to B25. From the Data tab, Data Tools, select Data Validation. In the Settings tab set the “Allow” criteria to List. Click on the icon in the Source box. Use your mouse to highlight cells F17:F25. Click OK.

Create a range name for the menu table. Call it Menu.

Select cells F17 to G25. Type the word Menu in the name box above column A. You will use this range name, instead of F178:G25, in the next task.

Create VLOOKUP Functions to find menu prices in cells C17 to C25.

In cell C17, enter a VLOOKUP Function to find menu prices. The input cell is B17. Use Menu, with no quotation marks, as the range argument. The column is 2. Copy the function into cells C18 to C25. The cells will show #N/A if nothing is ordered. Order some food.

Enter a VLOOKUP Function in cell C29 to find the sales tax rate for the county in cell B12. 

Enter a VLOOKUP Function in cell C30 to find the delivery fee for the county in cell B12.

Enter a VLOOKUP Function in cell C33 to find the delivery time for the county in Cell B12.

Examine the IF Function in cells D17-D25.

This function converts the #N/A error message into a zero, so the =SUM function in cell D26 will work.

Enter an IF Function in cell D28 to show the larger amount from cells D26 and D27.

Enter an IF Function in cell D30 to display the delivery fee or zero.

Format cell C33 to display the word Minutes after the number.

Right-click on cell C33. Choose Format Cells. On the number tab in the dialog box choose the Category “Custom.” In the Type box, enter # # “ minutes.” Include the quotation marks and the space before the word minutes. Click OK. This format tells Excel to show two numerals followed by the word minutes.

Enter some test data into the customer information. Use your real name.