Back to Worked Examples
Intermediate Spreadsheets

Spreadsheet Formulas

Problem Statement

A school canteen tracks sales data in a spreadsheet. Create formulas to: (a) Calculate total sales for each item, (b) Find the item with highest sales, (c) Calculate the average price, and (d) Show "Popular" if an item sold more than 50 units.

A B C D E
1 Item Price Qty Sold Total Status
2 Sandwich $5.50 65 ? ?
3 Juice $3.00 42 ? ?
4 Muffin $4.00 78 ? ?

Step-by-Step Solution

1

Calculate Total Sales (Price × Quantity)

In cell D2, multiply the price by quantity sold. Use cell references so the formula can be copied. 

=B2*C2

Result: $357.50 (5.50 × 65)

Tip: Copy this formula down to D3 and D4. The relative references will automatically adjust!

2

Create the Status formula using IF

In cell E2, use an IF function to show "Popular" if quantity sold is more than 50. 

=IF(C2>50, "Popular", "Regular")

How IF works:

  • • C2>50 is the condition to test
  • • "Popular" shows if the condition is TRUE
  • • "Regular" shows if the condition is FALSE
3

Find the maximum total sales

Use the MAX function to find the highest value in the Total column. 

=MAX(D2:D4)

Result: $357.50 (Sandwiches had the highest total sales)

4

Calculate the average price

Use the AVERAGE function on the Price column. 

=AVERAGE(B2:B4)

Result: $4.17 ((5.50 + 3.00 + 4.00) ÷ 3)

★

Bonus: Look up an item's price using VLOOKUP

If you want to find the price of "Juice" from the table: 

=VLOOKUP("Juice", A2:D4, 2, FALSE)

// Breakdown:
// "Juice"    = the value to find
// A2:D4      = the table range to search
// 2          = return value from column 2 (Price)
// FALSE      = exact match required

Result: $3.00

Complete Solution

A B C D E
1 Item Price Qty Sold Total Status
2 Sandwich $5.50 65 $357.50 Popular
3 Juice $3.00 42 $126.00 Regular
4 Muffin $4.00 78 $312.00 Popular
5 Summary
6 Highest Sales: $357.50 =MAX(D2:D4)
7 Average Price: $4.17 =AVERAGE(B2:B4)

Common Mistakes to Avoid

  • ✗ Forgetting the equals sign (=) at the start of formulas
  • ✗ Using wrong cell references when copying formulas (need $ for absolute references)
  • ✗ Forgetting quotes around text in IF functions ("Popular" not Popular)
  • ✗ Using commas instead of colons for ranges (A1:A10 not A1,A10)
  • ✗ VLOOKUP: Forgetting FALSE for exact match or using wrong column number