Chapter 6 - Excel Forms and Functions

Chapter 6 - Excel Forms and Functions. 1

Exercise 1 - Multiple Worksheet Practice. 3

Functions

Advanced Formulas: Excel has a built-in set of advanced formulas and functions. To access them, click on the fx symbol to the left of the formula bar, or click on the pull down box next to the AutoSum Icon, or use the Insert Menu. Here are some commonly used functions:

SUM Example = SUM(C3:G9)

Result: Total of all numbers in the range.

AVERAGE Example = AVERAGE(C3:G9)

Result: Average of all numbers in the range.

COUNTIF Example = COUNTIF(C3:G9, “>55”)

Result: # of cells in the range containing numbers greater than 55.

Used in counting groups of similar numbers within a set of data.

TODAY() Example = TODAY()

Result = current date. Useful for forms that require a date.

ROUND Example = ROUND(A4/3,2)

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

IF Example = IF(A4<4, “Small number”, 40)

The cell containing this function will display either Small Number, or 40, depending on the contents of cell A4.

VLOOKUP Example = VLookUp(C4,G3:K12,3)

The cell containing this function will display the number in the third column of the range G3 to K12, if the contents of cell C4 match an entry in one of the cells in the first column of the range.

IF Functions: We can use an IF Function to select what appears in a cell, choosing between two different options. Which option is displayed in the cell containing the IF function depends on the result of an equation. If the conditions in the equation are met, the first option is displayed. If not, the second option is displayed.

An IF function has 3 parts. The first part is an equation comparing a constant and a variable. The second and third parts show the two options for Excel to display. The variable in the equation is always entered into a different cell from the cell containing the IF function

EXAMPLE: = IF (A4 > 4, A4 + 2, 3) Cell A4 contains the variable, the number 4 is the constant. If our test condition is met, (i.e. A4 > 4), Excel selects the first formula. The cell containing the IF function will display the sum of the value in cell A4 and 2. Otherwise, the cell containing the IF function will show the number 3.

If cell C4 contains the IF function, here’s what would happen as the value in cell A4 is changed:

If cell A4 contains a 6, Excel displays 8, (the contents of cell A4 [6] + 2 ) in cell C4.
If cell A4 contains a 2, Excel displays the number 3 in cell C4.
If cell A4 contains a 4, Excel displays the number 3 in cell C4.

IF functions also work with strings (Text and/or numbers in quotes): =IF (A4>74%, ”Pass”, ”Fail”). In this example, if we put the IF function in cell C4, then cell C4 would display either Pass or Fail, depending on the value in cell A4.

Exercise 1 - Multiple Worksheet Practice

Create a form to track souvenir sales, edit it for each quarter, and create a mid-year summary.

Items sold = [Starting Inventory + Purchases – Ending Inventory.] Use an IF function to decide when to order more inventory. [IF (Starting Inv <100, order 144 more, if not, order zero more)]

Build the Generic Form

Rename the worksheets as Q1, Q2, and Summary.
Select all tabs. (Right click on any tab. Select All. The tabs all turn white.)
Create the form as shown below. It will automatically be copied into all of the worksheets.

	A	B	C	D	E	F
1	Souvenir Sales
2
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Mugs	Each		=IF(C4<100,144,0)		=C4+D4-E4
5	Shirts	Each		=IF(C5<100,144,0)		=C5+D5-E5
6	Posters	Each		=IF(C6<100,144,0)		=C6+D6-E6
7	CD’s	Each		=IF(C7<100,144,0)		=C7+D7-E7
8	Caps	Each		=IF(C8<100,144,0)		=C8+D8-E8

Complete the First Quarter (Q1) worksheet

Deselect all tabs. (click on a tab that is not showing in bold type.) Click on the Q1 tab.
Type “Quarter 1” in cell A2.
In column C, enter the Starting Inv values, as given below.
In column E, enter the Ending Inv numbers, as given below.
The If Functions in column D should tell you to order Posters and Caps, but nothing else.

	A	B	C	D	E	F
1	Souvenir Sales
2	Quarter 1
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Mugs	Each	260	=IF(C4<100,144,0)	52	=C4+D4-E4
5	Shirts	Each	160	=IF(C5<100,144,0)	104	=C5+D5-E5
6	Posters	Each	80	=IF(C6<100,144,0)	64	=C6+D6-E6
7	CD’s	Each	100	=IF(C7<100,144,0)	96	=C7+D7-E7
8	Caps	Each	60	=IF(C8<100,144,0)	132	=C8+D8-E8

To write formulas that refer to cells in other worksheets, include the name of the other worksheet, followed by an exclamation mark, in your cell reference.

For example: =Q2!E4 refers to cell E4 in the Q2 worksheet.

Complete the Second Quarter (Q2) worksheet

Type “Quarter 2” in cell A2.
The Starting Inv for each quarter = the Ending Inv from the previous quarter. The formula for the Starting Inv for Q2 Mugs is the Ending Inv for Q1 Mugs: =Q1!E4.
In column C, enter formulas for the Starting Inv. for each product.
In column E, enter a formula = to 1/4 * the (Starting Inv + Purchases) for each item.

Complete the Summary worksheet.

Change the subtitle in row 2 to Mid-Year Summary - followed by your name
The Starting Inv on the Mid-Year Summary = the first quarter’s Starting Inv.
The Ending Inv on the Mid-Year Summary = the second quarter’s Ending Inv.
Delete the formulas in the Purchases column.
Enter new formulas in the purchases column, equal to the sum of Purchases from the two quarters. For example, Mug Purchases =Q1!D4+Q2!D4.
You can point and click to build this formula. Start with an = sign in cell D4 in the Summary worksheet. Click on cell D4 in the Q1 worksheet. Type +. Click on cell D4 in the Q2 worksheet. Press Enter.
Copy the formula from D4 into rows 5-8.

	A	B	C	D	E	F
1	Souvenir Sales
2	Mid-Year Summary – My Name
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Mugs	Each	=Q1!C4		=Q2!E4	=C4+D4-E4
5	Shirts	Each	=Q1!C5		=Q2!E5	=C5+D5-E5
6	Posters	Each	=Q1!C6		=Q2!E6	=C6+D6-E6
7	CD’s	Each	=Q1!C7		=Q2!E7	=C7+D7-E7
8	Caps	Each	=Q1!C8		=Q2!E8	=C8+D8-E8

Your manager asks for more information on the Mid - Year Summary worksheet.

Add a column showing Price.
Make up a sales price for each item and enter it in the column
Add a column showing Unit Cost.
Make up a cost for each item and enter it in the column.
Add a column showing Revenue [= Items Sold * Price]
Add a column showing Total Cost. [= Items Sold * Unit Cost]
Add a column showing Profit. [= Revenue – Total Cost]
Format columns G-K as centered, Currency Style, with no decimal places.

Sample: www.cob.sjsu.edu/bus91L/textbook/ch6pics.htm

Exercise 2 – Delivery Form

From the Tools menu, Customize submenu, Options tab, select all options except “large icons.”

This assignment will introduce sorting and managing data using If Functions, lists, and VLookUp tables. You’ll integrate Excel with the web, work with multiple spreadsheets, and learn more about formatting.

Data used www.cob.sjsu.edu/BUS91L/Textbook/DeliveryForm.xls

DO NOT OPEN THE FILE. Save the file on your desktop. Then open the file.

Background

When you want a formula to refer to a cell (or range) in another worksheet, put the worksheet name followed by an ! sign in your formula before the cell address.

For example, =Lists!A5 refers to cell A5 in the Lists worksheet.

An IF formula has three parts. Part one is an equation comparing two values; one value is a constant and one is a variable. Part two is the result to display if the conditions in the equation are met. Part three is what to display if the conditions in the equation are not met.

A VLOOKUP formula has three parts. Part one is the cell where you find the text or number that will be looked for in the first column of your lookup table. Part two is the range where your lookup table is located. Part three is the column of information that contains the information you want to display if a match is found. Lookup Tables will have many rows of information, and will contain 2 or more columns.

Tasks:

Build the Lists Worksheet

Select the Lists tab.

Practice sorting lists of data

Highlight cells A16:B25. From the Data Menu, Select Sort. Sort by Price and Descending.
Highlight the same area. Use the A/Z icon to resort the list in alphabetical order.
Highlight the same area. Copy it. Click on cell D16. From the Edit menu, select Paste Special. Check Transpose. Click Ok. Press Escape.
Highlight cells D16:M17. From the Edit Menu, select Clear and All. You can use this menu to clear formats.

Study the County Look-Up table

The County Table will be used with VLOOKUP functions to find values based on the county you specify in the Invoice worksheet. The first column in VLookUp tables must be sorted alphabetically, A-Z.

Examine the function in cell B13 of the Lists worksheet: =VLOOKUP ( Invoice!B8, A5:D10 , 2) The first part of this function tells Excel where to find the name of the County - in cell B8 in the Invoice worksheet. The middle part of the function tells Excel where to find a table that stores information about counties – The range A5 to D10 in this worksheet. The third part of the function tells Excel which column in the table contains the information you want to display in this cell.

How it works: If the user enters Monterey in cell B8 of the Invoice worksheet, Excel will look in the first column of the Counties table to find a matching entry. If it finds a match, it will look in the row that contains the match, and go to column 2. The information in that column will be displayed in the cell containing the VLookUp formula.

Formulas in the Invoice worksheet will pull data from cells B13, C13, and D13 in the Lists worksheet. This data will change whenever the name of the county in cell B8 of the Invoice worksheet changes.

Build the Invoice Worksheet

Enter a function to automatically put a date into your invoice

In cell D3 enter this formula =today() Make sure there are no spaces between the parentheses.

Apply special number formatting

Select Cell B9 on the Invoices worksheet. Press Control and 1. Select the Number Tab. Choose the Category “Special” and the Type “Phone Number”.

Create a pull-down list of county names

Select Cell F4. Enter this formula =Lists!A5. Copy this formula down into cells F5 to F9.
Select cell B8. From the Data Menu, select Validation and the Settings tab. For the “Allow” criteria choose List. Click in the Source box. Use your mouse to highlight cells F4:F9. Click OK.

Create a second pull-down list to show menu items

In cell F13, enter the formula =Lists!A17. Copy the formula down into cells F14 to F21.

Apply the menu items pull-down list to multiple cells

Select cells B13 to B21. From the Data Menu, select Data Validation and the Settings tab. For the “Allow” criteria choose List. Click in the Source box. Use your mouse to highlight cells F13:F21. Click OK

Use a lookup function to match prices with menu items

In cell C13, enter the formula =VLOOKUP(B13,Lists!A$17:B$25,2). This function will take the food item entered in cell B13, compare it to the table found in the Lists worksheet, and report back with the menu price found in column 2.
Copy the formula down into cells C14 through C21.

Use an IF statement to calculate the minimum order fee.

In cell D23, enter this formula =IF(D22>10,0,10-D22)

Get the sales tax rate for your county (From the look up table on the Lists tab.)

In cell C25 enter this formula =Lists!B13.

Use an IF statement to calculate the Delivery Fee.

In cell D26 enter this formula =IF(D24>50,0,C26)

Cell C26 shows the delivery fee for the county specified in cell B8.

Excel found this in the third column of the County Table using VLookUp.

Find the total of the invoice

In cell D27 enter this formula = SUM(D24:D26)

Create a link to MapQuest for your driver to use.

In cell A31, type in www.mapquest.com. This automatically formats as a web-link.

Save the file on the desktop as InvoiceTemplate

Enter some test data into the invoice form.

Enter data into the customer area. .
Enter a sample order in cells A13 to A16. Change the order to see how it affects the total bill.
Change the county (Cell B8) and see what happens to the taxes, delivery fee and travel time.

Home Table of Contents Next