Mixed references in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to calculate total sales commissions for each salesperson based on their sales amount and a fixed commission rate per product category. The commission rate is the same for all salespeople but varies by category.

📊 Data: You have a table listing salespeople, product categories, and sales amounts. You also have a separate table with commission rates for each product category.

🎯 Deliverable: Create a formula using mixed references to calculate the commission for each sale, then sum commissions per salesperson.

Sample Data

Salesperson	Category	Sales Amount
Alice	Electronics	1200
Bob	Furniture	850
Alice	Furniture	600
Charlie	Electronics	700
Bob	Office Supplies	300
Charlie	Furniture	400
Alice	Office Supplies	150

Category	Commission Rate
Electronics	0.05
Furniture	0.07
Office Supplies	0.03

1

Step 1: Add a new column named 'Commission' next to 'Sales Amount' to calculate commission for each sale.

In the first row of the new 'Commission' column (e.g., D2), enter the formula: =C2 * VLOOKUP(B2, $F$2:$G$4, 2, FALSE)

Expected Result

For Alice's Electronics sale of 1200, commission is 1200 * 0.05 = 60

2

Step 2: Copy the formula down the 'Commission' column for all sales rows.

Drag the fill handle from D2 down to D8 to apply the formula to all rows.

Expected Result

Each row shows the correct commission based on sales amount and category commission rate.

3

Step 3: Create a summary table listing each salesperson and their total commission.

List unique salespeople in a new column (e.g., H2:H4: Alice, Bob, Charlie). In I2, enter: =SUMIF(A$2:A$8, H2, D$2:D$8)

Expected Result

Alice's total commission sums to 60 + 42 + 4.5 = 106.5

4

Step 4: Copy the SUMIF formula down for all salespeople in the summary table.

Drag the fill handle from I2 down to I4.

Expected Result

Bob's total commission is 68.5; Charlie's total commission is 63.

Final Result

Salesperson  Category       Sales Amount  Commission
--------------------------------------------------
Alice        Electronics    1200          60
Bob          Furniture      850           59.5
Alice        Furniture      600           42
Charlie      Electronics    700           35
Bob          Office Supplies 300          9
Charlie      Furniture      400           28
Alice        Office Supplies 150          4.5

Summary:
Salesperson  Total Commission
-----------------------------
Alice        106.5
Bob          68.5
Charlie      63

✓Alice earned the highest total commission of 106.5.

✓Furniture category has the highest commission rate (7%), impacting commissions significantly.

✓Using mixed references in the VLOOKUP formula allows easy copying without changing the commission rate table range.

Bonus Challenge

Modify the commission calculation to include a bonus of $50 for sales amounts over $1000.

Show Hint

Use an IF formula combined with your existing commission formula, e.g., =C2 * VLOOKUP(B2, $F$2:$G$4, 2, FALSE) + IF(C2>1000, 50, 0)

Practice

1. What does a mixed reference like $A1 mean in Google Sheets?

easy

A. Neither the column nor the row is fixed; both change when copied.

B. The column A is fixed, but the row number changes when copied.

C. Both the column and row are fixed and do not change when copied.

D. The row 1 is fixed, but the column letter changes when copied.

Solution

1. Step 1: Understand the dollar sign placement

   In $A1, the dollar sign before A means the column is fixed.

2. Step 2: Analyze the row number

   The row number 1 has no dollar sign, so it changes when copied.

3. Final Answer:

   The column A is fixed, but the row number changes when copied. -> Option B

4. Quick Check:

   Mixed reference $A1 fixes column A [OK]

Hint: Dollar before column fixes column; before row fixes row [OK]

Common Mistakes:

• Thinking $ fixes both row and column
• Confusing which part the $ fixes
• Assuming no change happens when copying

2. Which of the following is the correct syntax for a mixed reference that fixes the row but allows the column to change?

easy

A. A$1

B. $A$1

C. $A1

D. A1$

Solution

1. Step 1: Identify the dollar sign position for fixing row

   To fix the row, place $ before the row number, like A$1.

2. Step 2: Check other options

   $A$1 fixes both row and column, $A1 fixes column only, and A1$ is invalid syntax.

3. Final Answer:

   A$1 -> Option A

4. Quick Check:

   Dollar before row number fixes row [OK]

Hint: Put $ before row number to fix row only [OK]

Common Mistakes:

• Placing $ after the row number (invalid)
• Fixing both row and column unintentionally
• Confusing column and row positions

3. Given the formula =B$2 + $C3 in cell D4, what will be the formula in cell D5 after copying it down one row?

medium

A. =B$2 + $C4

B. =B$3 + $C4

C. =B$3 + $C3

D. =B$2 + $C3

Solution

1. Step 1: Analyze the first reference B$2

   Column B is relative, row 2 is fixed by $. Copying down one row keeps row 2 fixed, so it stays B$2.

2. Step 2: Analyze the second reference $C3

   Column C is fixed by $, row 3 is relative. Copying down one row changes row 3 to 4, so it becomes $C4.

3. Final Answer:

   =B$2 + $C4 -> Option A

4. Quick Check:

   Mixed references adjust only unfixed parts [OK]

Hint: Only parts without $ change when copied [OK]

Common Mistakes:

• Changing fixed rows or columns
• Not updating relative row numbers
• Confusing which part is fixed

4. Given the formula =SUM($A1:B$2) in cell C3. When copying this formula to cell D4, what will be the formula?

medium

A. =SUM($A1:B$2)

B. =SUM($A1:C$2)

C. =SUM($A2:B$2)

D. =SUM($A2:C$2)

Solution

1. Step 1: Understand how references change when copied

   Copying from C3 to D4 moves one column right and one row down. $A1 changes to $A2 (row changes), B$2 changes to C$2 (column changes).

2. Step 2: Check the resulting range

   The range becomes $A2:C$2.

3. Final Answer:

   =SUM($A2:C$2) -> Option D

4. Quick Check:

   Mixed references adjust range correctly [OK]

Hint: Trace relative column/row shifts separately [OK]

Common Mistakes:

• Forgetting to adjust relative row from 1 to 2
• Not updating relative column from B to C
• Assuming no changes when copying
• Confusing fixed vs relative parts

5. You want to create a formula in cell B2 that multiplies the value in column A of the same row by the value in row 1 of the same column, and then copy this formula across and down the table. Which formula uses mixed references correctly?

hard

A. =$A$2*B1

B. =A2*$B$1

C. =$A2*B$1

D. =A$2*$B1

Solution

1. Step 1: Fix the column for the value in column A

   The value in column A of the same row means column A fixed, row relative: $A2.

2. Step 2: Fix the row for the value in row 1 of the same column

   The value in row 1 of the same column means row 1 fixed, column relative: B$1.

3. Step 3: Combine for multiplication

   The formula is =$A2*B$1, which copies correctly across and down.

4. Final Answer:

   =$A2*B$1 -> Option C

5. Quick Check:

   Fix column for A, fix row for 1 [OK]

Hint: Fix column for A, fix row for 1 to copy correctly [OK]

Common Mistakes:

• Fixing both row and column incorrectly
• Not fixing the correct part of the reference
• Using absolute references that prevent copying