3.7 Chapter Scored
midasCoffee Company
Ruth Kobran owns a coffee supply company named MidasCoffee. She needs some help writing the formulas for the order form she uses to invoice customers. You will to enter VLOOKUP functions to to fill in the description and unit price on the order form. Additionally will need to write the formulas for all of the calculations on the form. Some of the more complex parts are determining if the customer will get a discount (based on the customer status) as well as the shipping charge (orders over $199 get free shipping). You will use IF functions for both of those calculations. Then you will analyze some sales data for the six MidasCoffee company locations in the Portland area by creating two PivotTables that will answer two different questions about the sales data.
Prepare the Worksheet
- Open the SC3 Data workbook and save the workbook as SC3 MidasCoffee.
- Enter the following order information:
Order #: 56894
Order Date: use a function that displays the current date - Enter the following Billing Information:
Samantha Raitt
4270 SW Cooper Ln
Portland, OR 97225
503-674-1632
<EMAIL_ADDRESS>- For the Shipping Information, create formulas using cell references to display the corresponding information from the Billing Information section. For example, the Customer cell will display the name of the customer in cell C11.
- In the range B19:B22 and D19:D22 enter the following item numbers and Quantities:
Create Formulas and Functions
- In cell C19 enter a VLOOKUP function that will return the description of the item number in cell B19. The Lookup Table in on the Lookup Table sheet.
- Copy/fill this formula down through row 25. Excel will return the #N/A error message for any rows that do not have an item # entered.
- To fix the error messages, we’ll add the IFERROR function to the VLOOKUP function. The IFERROR function will insert what we tell it to in the cell if an error message appears.
- Click in cell C19 to make it active.
- In the formula bar, click right after the equal sign in the VLOOKUP function.
- Key in the letters if to bring up the IFERROR function and double click on it to select it.
- Move to the very end of the function, after the closing parenthesis.
- Type a comma, then two quotation marks (“”). The two quotation marks mean to insert a blank space.
- Type the closing parenthesis mark and push the Enter key on your keyboard.
- The formula will look like this: =IFERROR(VLOOKUP(Lookup value, Table array, Col index num, FALSE),””)
- Refill the function down through row 25. This should eliminate the error messages. If not, repeat the above steps.
- In cell E19 enter another VLOOKUP function to retrieve the Unit Price from the Lookup Table.
- Edit the VLOOKUP function in cell E19 to add the IFERROR function that same as you did for cell C19
- Fill the function down through row 25.
- In cell F19, enter an IF function that tests whether the order quantity in cell D19 is greater than 0 (zero). lf it is, return