Calculated fields in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate Total Price with Tax

You have a price in cell A2 and a tax rate in cell B2 (as a decimal). Which formula in cell C2 correctly calculates the total price including tax?

A =A2 + B2

B =A2 * (1 + B2)

C =A2 * B2

D =A2 / (1 - B2)

💡 Hint

Remember to add the tax to the original price, not just the tax amount.

The total price including tax is the original price plus the tax amount. The tax amount is the price multiplied by the tax rate, so the formula is =A2 * (1 + B2).

📊 Formula Result

intermediate

Calculate Average Score Excluding Zeroes

You have scores in cells A2 to A6. Which formula in cell B2 calculates the average of only the scores greater than zero?

A =SUM(A2:A6)/COUNT(A2:A6)

B =AVERAGE(A2:A6)

C =AVERAGEIF(A2:A6, ">0")

D =SUMIF(A2:A6, ">0")/COUNT(A2:A6)

💡 Hint

Use a function that averages only cells meeting a condition.

AVERAGEIF(range, condition) calculates the average of cells that meet the condition. Here, it averages only scores greater than zero.

❓ Function Choice

advanced

Choose the Correct Formula for Dynamic Discount

You want to apply a 10% discount if the quantity in B2 is more than 10, otherwise no discount. Which formula in C2 correctly calculates the discounted price based on price in A2?

A =A2 * IF(B2 < 10, 0.9, 1)

B =A2 * IF(B2 >= 10, 0.9, 1)

C =A2 * IF(B2 > 10, 1, 0.9)

D =A2 * IF(B2 > 10, 0.9, 1)

💡 Hint

Discount applies only when quantity is strictly greater than 10.

The discount is 10% off (multiply by 0.9) only if quantity is more than 10. Otherwise, price stays the same (multiply by 1). So the formula is =A2 * IF(B2 > 10, 0.9, 1).

❓ data_analysis

advanced

Calculate Total Sales with Multiple Conditions

You have sales data with amounts in column A and regions in column B. Which formula calculates the total sales in column A only for the region "East"?

A =SUMIFS(A:A, B:B, "East")

B =SUMIF(A:A, "East", B:B)

C =SUMIF(B:B, "East", A:A)

D =SUMIFS(B:B, A:A, "East")

💡 Hint

Use the function that sums with multiple criteria.

SUMIFS(sum_range, criteria_range1, criteria1) sums values in sum_range where criteria_range1 meets criteria1. Here, sum sales in A:A where region in B:B is "East".

🎯 Scenario

expert

Create a Formula to Calculate Commission Based on Sales Brackets

You want to calculate commission in cell C2 based on sales in A2: 5% for sales up to 1000, 10% for sales between 1001 and 5000, and 15% for sales above 5000. Which formula correctly calculates the commission?

A =IF(A2 <= 1000, A2 * 0.05, IF(A2 <= 5000, A2 * 0.1, A2 * 0.15))

B =IF(A2 <= 1000, A2 * 0.05, IF(A2 < 5000, A2 * 0.1, A2 * 0.15))

C =IF(A2 < 1000, A2 * 0.05, IF(A2 < 5000, A2 * 0.1, A2 * 0.15))

D =IF(A2 < 1000, A2 * 0.05, IF(A2 <= 5000, A2 * 0.1, A2 * 0.15))

💡 Hint

Use nested IFs with correct boundary conditions including equal signs.

The commission brackets include sales up to 1000 (≤1000), between 1001 and 5000 (≤5000), and above 5000. The formula must use ≤ for correct boundaries: =IF(A2 <= 1000, A2 * 0.05, IF(A2 <= 5000, A2 * 0.1, A2 * 0.15)).

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