Page 222 - Ms Excel Note and Workbook

MICROSOFT EXCEL NOTE AND WORKBOOK

7. Using IF formula, determine the “DISCOUNT”. If quantity is less than 10, there is NO
discount but if more than 10, user will get the discount. Use the formula:
=IF(D5>10, “YES”,”NO”)

8. At cell C15, create a drop-down list for “TYPE” using the LIST function like figure
below.

9. Using VLOOKUP formula, find the matches “MONTH” in cell C17 and “TOTAL
PRICE” in cell C18 when user choose the TYPE at cell C15. Use the formula:
For MONTH:
=VLOOKUP(C15,B5:F11,2,FALSE)

For TOTAL PRICE:
=VLOOKUP(C15,B5:F11,5,FALSE)

10. Create a PIVOT TABLE:
i) Find how many items for each MONTH were purchased. Place your Pivot
Table in new worksheet and rename it as Pivot table 1.
ii) Find the purchase frequency for each month. Place your Pivot Table in new
worksheet and rename it as Pivot table 2.

213 | P a g e