Unit 1 Pc Market Distribution
-------------------------------------------------
-------------------------------------------------
-------------------------------------------------
New Perspectives excel 2010
-------------------------------------------------
Tutorial 7: Case Problem 1
PC-Market Distribution
Skills
Insert calculated columns in an Excel table Use the IF function Use structured references to create formulas in Excel tables Create nested IFs Use the VLOOKUP function to find and exact match Use the VLOOKUP function to find an approximate match Using the IFERROR function Highlight duplicate values Summarize data conditionally Use the COUNTIF function Use the AVERAGEIF function
Project overview Linda Klaussen works for PC-Market Distribution, a computer supply store. She needs your …show more content…
Enter the following information in the Purchase Order worksheet: a) In the range D40:D42, enter labels Standard, Express, and Overnight (in that order). b) In the range E40:E42 enter the costs $9.50, $14.50, and $18.50 (in that order). c) In cell B15, enter Express.
5. In cell C17, use nested IF functions to display the shipping costs for the option entered in cell B15. a) The first test should determine if the shipping option in cell B15 is blank, and if so, then cell C17 should be blank. b) Then a nested IF should test if B15 is equal to the shipping option in D40, and if so, then the value in E40 should be used. c) Then a nested IF should test if B15 is equal to the shipping option in D41, and if so, then the value in E41 should be used, otherwise the value in E42 should be used.
6. In cell C19, enter a formula to display the total cost of the product (price times quantity) plus shipping. In cell B12, enter 4.
7. In cell C19, use the IFERROR function to modify the formula so that if the cell equals an error value, then the message Check Product ID, Quantity, or Shipping option is displayed in the
-------------------------------------------------
-------------------------------------------------
New Perspectives excel 2010
-------------------------------------------------
Tutorial 7: Case Problem 1
PC-Market Distribution
Skills
Insert calculated columns in an Excel table Use the IF function Use structured references to create formulas in Excel tables Create nested IFs Use the VLOOKUP function to find and exact match Use the VLOOKUP function to find an approximate match Using the IFERROR function Highlight duplicate values Summarize data conditionally Use the COUNTIF function Use the AVERAGEIF function
Project overview Linda Klaussen works for PC-Market Distribution, a computer supply store. She needs your …show more content…
Enter the following information in the Purchase Order worksheet: a) In the range D40:D42, enter labels Standard, Express, and Overnight (in that order). b) In the range E40:E42 enter the costs $9.50, $14.50, and $18.50 (in that order). c) In cell B15, enter Express.
5. In cell C17, use nested IF functions to display the shipping costs for the option entered in cell B15. a) The first test should determine if the shipping option in cell B15 is blank, and if so, then cell C17 should be blank. b) Then a nested IF should test if B15 is equal to the shipping option in D40, and if so, then the value in E40 should be used. c) Then a nested IF should test if B15 is equal to the shipping option in D41, and if so, then the value in E41 should be used, otherwise the value in E42 should be used.
6. In cell C19, enter a formula to display the total cost of the product (price times quantity) plus shipping. In cell B12, enter 4.
7. In cell C19, use the IFERROR function to modify the formula so that if the cell equals an error value, then the message Check Product ID, Quantity, or Shipping option is displayed in the