Calculated fields in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing total sales, total cost, and profit for each product category.

📊 Data: You have a sales data table with columns: Product, Category, Units Sold, Unit Price, and Unit Cost.

🎯 Deliverable: Create a summary table with Category, Total Sales, Total Cost, and Profit calculated using formulas.

Sample Data

Product	Category	Units Sold	Unit Price	Unit Cost
Shirt	Clothing	10	20	12
Jeans	Clothing	5	40	25
Hat	Accessories	8	15	7
Belt	Accessories	6	18	10
Notebook	Stationery	20	5	2
Pen	Stationery	50	2	1

1

Step 1: Create a list of unique product categories in a new column starting at G2.

=UNIQUE(B2:B7)

Expected Result

Clothing, Accessories, Stationery listed in cells G2, G3, G4

2

Step 2: Calculate Total Sales for each category in column H starting at H2. Total Sales = Units Sold * Unit Price summed by category.

=SUMPRODUCT((B$2:B$7=G2)*(C$2:C$7)*(D$2:D$7))

Expected Result

Clothing: 10*20 + 5*40 = 200 + 200 = 400 in H2

3

Step 3: Calculate Total Cost for each category in column I starting at I2. Total Cost = Units Sold * Unit Cost summed by category.

=SUMPRODUCT((B$2:B$7=G2)*(C$2:C$7)*(E$2:E$7))

Expected Result

Clothing: 10*12 + 5*25 = 120 + 125 = 245 in I2

4

Step 4: Calculate Profit for each category in column J starting at J2. Profit = Total Sales - Total Cost.

=H2 - I2

Expected Result

Clothing: 400 - 245 = 155 in J2

5

Step 5: Copy formulas in H2, I2, and J2 down for all categories in G3 and G4.

Copy formulas down to rows 3 and 4

Expected Result

Accessories Total Sales=8*15+6*18=120+108=228; Total Cost=8*7+6*10=56+60=116; Profit=112 Stationery Total Sales=20*5+50*2=100+100=200; Total Cost=20*2+50*1=40+50=90; Profit=110

Final Result

Category    Total Sales    Total Cost    Profit
Clothing        400           245         155
Accessories     228           116         112
Stationery      200            90         110

✓Clothing category has the highest total sales and profit.

✓Accessories have moderate sales and profit.

✓Stationery has lower sales but good profit margin.

Bonus Challenge

Add a new calculated field showing Profit Margin (%) for each category.

Show Hint

Use formula: Profit Margin = (Profit / Total Sales) * 100

Practice

1. What does a calculated field in Google Sheets do?

easy

A. Deletes data from the spreadsheet

B. Creates new values by using formulas on existing cells

C. Changes the font style of cells

D. Saves the spreadsheet automatically

Solution

1. Step 1: Understand the purpose of calculated fields

   Calculated fields use formulas to create new values based on existing data in cells.

2. Step 2: Compare options with this purpose

   Only Creates new values by using formulas on existing cells describes creating new values using formulas, which matches the definition.

3. Final Answer:

   Creates new values by using formulas on existing cells -> Option B

4. Quick Check:

   Calculated field = new values from formulas [OK]

Hint: Calculated fields always create new values from existing data [OK]

Common Mistakes:

• Confusing formatting changes with calculated fields
• Thinking calculated fields delete data
• Assuming calculated fields save files

2. Which of these formulas correctly calculates the total price if quantity is in A2 and price per item is in B2?

easy

A. =A2*B2

B. =A2+B2

C. =A2-B2

D. =A2/B2

Solution

1. Step 1: Identify the calculation needed

   Total price = quantity x price per item, so multiply A2 by B2.

2. Step 2: Match formula to calculation

   Only formula =A2*B2 multiplies the two cells correctly.

3. Final Answer:

   =A2*B2 -> Option A

4. Quick Check:

   Multiply quantity and price = total price [OK]

Hint: Use * to multiply quantity and price for total [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Dividing instead of multiplying
• Subtracting values instead of multiplying

3. Given the formula =C2*D2+E2 where C2=5, D2=3, and E2=10, what is the result?

medium

A. 25

B. 15

C. 25.0

D. 18

Solution

1. Step 1: Calculate multiplication part

   Multiply C2 and D2: 5 * 3 = 15.

2. Step 2: Add E2 value

   Add 15 + 10 = 25.

3. Final Answer:

   25 -> Option A

4. Quick Check:

   5*3+10 = 25 [OK]

Hint: Multiply first, then add according to formula order [OK]

Common Mistakes:

• Adding before multiplying
• Ignoring order of operations
• Confusing cell values

4. The formula =A2*B2+C2 gives wrong results. Which fix is correct if you want to add B2 and C2 first, then multiply by A2?

medium

A. =A2*B2*C2

B. =(A2*B2)+C2

C. =A2+B2+C2

D. =A2*(B2+C2)

Solution

1. Step 1: Understand the desired calculation

   You want to add B2 and C2 first, then multiply by A2.

2. Step 2: Use parentheses to change order

   Formula =A2*(B2+C2) adds B2 and C2 first, then multiplies by A2.

3. Final Answer:

   =A2*(B2+C2) -> Option D

4. Quick Check:

   Parentheses change order to add first [OK]

Hint: Use parentheses to control calculation order [OK]

Common Mistakes:

• Ignoring parentheses and wrong order
• Adding all values instead of multiplying
• Multiplying all values without addition

5. You have a list of sales in column A and a fixed tax rate of 8% in cell B1. Which formula in column B calculates the total price including tax for each sale in A2, A3, etc.?

hard

A. =A2 + B1

B. =A2 / B1

C. =A2 * (1 + B1)

D. =A2 * B1

Solution

1. Step 1: Understand the calculation for total price with tax

   Total price = sale amount + tax amount. Tax amount = sale x tax rate.

2. Step 2: Write formula combining sale and tax

   Total price = sale x (1 + tax rate). So formula is =A2*(1+B1).

3. Final Answer:

   =A2 * (1 + B1) -> Option C

4. Quick Check:

   Multiply sale by (1 + tax rate) for total [OK]

Hint: Multiply by (1 + tax rate) to add tax [OK]

Common Mistakes:

• Adding tax rate directly instead of multiplying
• Multiplying only by tax rate (missing original amount)
• Dividing by tax rate