Calculated fields in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate Total Sales with Tax

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

A =A2 + B2

B =A2 * B2

C =A2 / (1 - B2)

D =A2 * (1 + B2)

💡 Hint

Think about adding the tax percentage to the original amount.

The total sales including tax is the original amount plus the tax. Multiplying by (1 + tax rate) adds the tax correctly.

❓ Function Choice

intermediate

Choose the Correct Formula for Average Price

You have quantities sold in column A and total sales in column B. Which formula calculates the average price per unit sold in cell C2?

A =B2 / A2

B =AVERAGE(A2,B2)

C =SUM(A2:B2)

D =A2 / B2

💡 Hint

Average price is total sales divided by quantity sold.

Dividing total sales by quantity gives the average price per unit.

🎯 Scenario

advanced

Calculate Commission Based on Sales Threshold

You want to calculate commission in cell C2 based on sales in A2. The commission is 5% if sales are up to $1000, and 10% for any amount above $1000. Which formula correctly calculates the commission?

A =IF(A2>1000, A2*0.10, A2*0.05)

B =IF(A2<=1000, A2*0.05, (A2-1000)*0.10 + 1000*0.05)

C =A2*0.05 + IF(A2>1000, A2*0.10, 0)

D =IF(A2<=1000, A2*0.10, (A2-1000)*0.05 + 1000*0.10)

💡 Hint

Calculate commission separately for amounts up to and above $1000.

For sales up to $1000, commission is 5%. For sales above $1000, commission is 5% on first $1000 plus 10% on the rest.

📊 Formula Result

advanced

Calculate Weighted Average Price

You have quantities in A2:A4 and prices in B2:B4. Which formula in cell C2 calculates the weighted average price?

A =SUM(A2:A4) / SUM(B2:B4)

B =AVERAGE(B2:B4)

C =SUMPRODUCT(A2:A4, B2:B4) / SUM(A2:A4)

D =SUMPRODUCT(B2:B4) / SUM(A2:A4)

💡 Hint

Weighted average multiplies each price by its quantity, sums those, then divides by total quantity.

SUMPRODUCT multiplies quantities and prices, then dividing by total quantity gives weighted average price.

❓ data_analysis

expert

Identify the Correct Formula for Dynamic Profit Margin

You have sales in A2, cost in B2, and want to calculate profit margin percentage in C2. The profit margin is (Sales - Cost) divided by Sales, shown as a percentage. Which formula correctly calculates this with error handling for zero sales?

A =IFERROR((A2-B2)/A2, 0)

B =IF(A2=0, 0, (A2-B2)/A2)

C =(A2-B2)/A2

D =IF(A2=0, "Error", (A2-B2)/A2)

💡 Hint

Use a formula that avoids division errors and returns 0 if sales are zero.

IFERROR catches division by zero and returns 0, ensuring no error appears in the cell.