Exercise 1: Boolean Formulas and IF Functions
Exercise 2: Ajax Steak and Eggs Restaurant - Order Form
Exercise 3: Java Juice Delivery Form
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 |
|
|
|
|
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.
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:
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.
|
|
|||
|
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
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
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.