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)).