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.

Practice

1. What is a calculated field in Excel?

easy

A. A filter to hide rows

B. A cell that contains only text

C. A chart that shows data trends

D. A formula that creates new values from existing data

Solution

1. Step 1: Understand the definition of calculated fields

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

2. Step 2: Identify the correct description

   Only A formula that creates new values from existing data describes a formula creating new values from data, which matches the definition.

3. Final Answer:

   A formula that creates new values from existing data -> Option D

4. Quick Check:

   Calculated field = formula creating new values [OK]

Hint: Calculated fields always involve formulas and existing data [OK]

Common Mistakes:

• Confusing calculated fields with charts
• Thinking calculated fields are just text
• Mixing calculated fields with filters

2. Which of these is the correct formula to calculate 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. =SUM(A2,B2)

Solution

1. Step 1: Understand the calculation needed

   Total price equals quantity multiplied by price per item, so multiply A2 by B2.

2. Step 2: Identify the correct formula syntax

   =A2*B2 correctly multiplies the two cells. Other options add, subtract, or sum incorrectly.

3. Final Answer:

   =A2*B2 -> Option A

4. Quick Check:

   Multiply quantity by price = =A2*B2 [OK]

Hint: Use * to multiply quantities and prices in formulas [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Using SUM for multiplication
• Subtracting instead of multiplying

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

medium

A. 53

B. 50

C. 80

D. 18

Solution

1. Step 1: Calculate multiplication part

   Multiply C2 and D2: 5 * 10 = 50.

2. Step 2: Add E2 value

   Add E2 (3) to 50: 50 + 3 = 53.

3. Final Answer:

   53 -> Option A

4. Quick Check:

   5*10+3 = 53 [OK]

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

Common Mistakes:

• Adding before multiplying
• Ignoring E2 value
• Misreading cell values

4. You wrote the formula =A2*B2+ but Excel shows an error. What is the fix?

medium

A. Add a closing parenthesis

B. Add a cell reference after the plus sign

C. Change * to +

D. Remove the plus sign at the end

Solution

1. Step 1: Identify the formula error

   The formula ends with a plus sign (+) but no value or cell after it, causing a syntax error.

2. Step 2: Fix the formula by completing it

   Add a cell reference or number after the plus sign to complete the formula, e.g., =A2*B2+C2.

3. Final Answer:

   Add a cell reference after the plus sign -> Option B

4. Quick Check:

   Formula must be complete, no trailing operators [OK]

Hint: Never end formulas with an operator without a value [OK]

Common Mistakes:

• Removing operator without replacement
• Adding parentheses without matching pairs
• Changing operators incorrectly

5. You have a table with columns Quantity (A), Price (B), and Discount % (C). Which formula in D2 calculates the final price after discount?

hard

A. =A2*B2/C2

B. =A2*B2-C2

C. =A2*B2*(1-C2)

D. =A2*B2+C2

Solution

1. Step 1: Understand the calculation for discount

   Final price = quantity * price * (1 - discount rate). Discount % is in decimal form (e.g., 0.2 for 20%).

2. Step 2: Choose the formula matching this logic

   =A2*B2*(1-C2) correctly applies discount by multiplying total price by (1 - discount).

3. Final Answer:

   =A2*B2*(1-C2) -> Option C

4. Quick Check:

   Apply discount by multiplying total by (1 - discount) [OK]

Hint: Use (1 - discount) to reduce price by discount percent [OK]

Common Mistakes:

• Subtracting discount directly without multiplying
• Adding discount instead of subtracting
• Dividing by discount incorrectly