Arithmetic operators in formulas in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a small retail store.

📋 Request: Your manager wants you to calculate the total price for each product sold, including tax, and find the overall total sales.

📊 Data: You have a list of products sold with their unit price and quantity sold. The tax rate is 8%.

🎯 Deliverable: Create a spreadsheet that calculates the total price before tax, tax amount, total price after tax for each product, and the overall total sales.

Sample Data

Product	Unit Price	Quantity Sold
Notebook	2.50	10
Pen	1.20	15
Eraser	0.50	20
Marker	3.00	5
Folder	4.00	7

1

Step 1: Calculate the total price before tax for each product by multiplying Unit Price by Quantity Sold.

In cell D2, enter =B2*C2 and copy down for all products.

Expected Result

For Notebook: 2.50*10 = 25.00; Pen: 1.20*15 = 18.00; Eraser: 0.50*20 = 10.00; Marker: 3.00*5 = 15.00; Folder: 4.00*7 = 28.00

2

Step 2: Calculate the tax amount for each product by multiplying the total price before tax by 8%.

In cell E2, enter =D2*0.08 and copy down for all products.

Expected Result

For Notebook: 25.00*0.08 = 2.00; Pen: 18.00*0.08 = 1.44; Eraser: 10.00*0.08 = 0.80; Marker: 15.00*0.08 = 1.20; Folder: 28.00*0.08 = 2.24

3

Step 3: Calculate the total price after tax for each product by adding the total price before tax and the tax amount.

In cell F2, enter =D2+E2 and copy down for all products.

Expected Result

For Notebook: 25.00+2.00 = 27.00; Pen: 18.00+1.44 = 19.44; Eraser: 10.00+0.80 = 10.80; Marker: 15.00+1.20 = 16.20; Folder: 28.00+2.24 = 30.24

4

Step 4: Calculate the overall total sales by summing the total price after tax for all products.

In cell F7, enter =SUM(F2:F6).

Expected Result

Overall total sales = 27.00 + 19.44 + 10.80 + 16.20 + 30.24 = 103.68

Final Result

Product   Unit Price  Quantity Sold  Total Before Tax  Tax Amount  Total After Tax
Notebook      2.50           10           25.00           2.00          27.00
Pen           1.20           15           18.00           1.44          19.44
Eraser        0.50           20           10.00           0.80          10.80
Marker        3.00            5           15.00           1.20          16.20
Folder        4.00            7           28.00           2.24          30.24

Overall Total Sales: 103.68

✓Multiplying unit price by quantity gives total before tax.

✓Tax is 8% of the total before tax.

✓Adding tax to total before tax gives total after tax.

✓Overall total sales sum all total after tax values.

Bonus Challenge

Add a discount column that applies a 10% discount on the total before tax if quantity sold is more than 10, then recalculate tax and total after tax accordingly.

Show Hint

Use an IF formula to check if quantity is greater than 10, then multiply total before tax by 0.9 for discount.

Practice

1. Which arithmetic operator in Excel is used for multiplication in a formula like =A1 * B1?

easy

A. + (plus)

B. * (asterisk)

C. - (minus)

D. / (slash)

Solution

1. Step 1: Identify the multiplication operator

   In Excel formulas, the asterisk (*) is used to multiply values.

2. Step 2: Confirm the operator in the example

   The formula =A1 * B1 uses the asterisk to multiply the values in cells A1 and B1.

3. Final Answer:

   * (asterisk) -> Option B

4. Quick Check:

   Multiplication = * [OK]

Hint: Remember * means multiply in Excel formulas [OK]

Common Mistakes:

• Confusing * with + for addition
• Using x instead of *
• Using / for multiplication

2. Which of the following is the correct formula to add values in cells A1 and B1 in Excel?

easy

A. =A1 + B1

B. =A1 - B1

C. =A1 * B1

D. =A1 / B1

Solution

1. Step 1: Identify the addition operator

   The plus sign (+) is used to add numbers in Excel formulas.

2. Step 2: Check the formula syntax

   The formula =A1 + B1 correctly adds the values in cells A1 and B1.

3. Final Answer:

   =A1 + B1 -> Option A

4. Quick Check:

   Addition = + [OK]

Hint: Use + to add numbers in Excel formulas [OK]

Common Mistakes:

• Using - instead of +
• Forgetting the = sign
• Using * for addition

3. What is the result of the formula =10 + 5 * 2 in Excel?

medium

A. 25

B. 30

C. 15

D. 20

Solution

1. Step 1: Apply order of operations

   Excel calculates multiplication before addition, so 5 * 2 = 10.

2. Step 2: Add the results

   Then add 10 + 10 = 20.

3. Final Answer:

   20 -> Option D

4. Quick Check:

   10 + (5 * 2) = 20 [OK]

Hint: Multiply before adding unless parentheses change order [OK]

Common Mistakes:

• Adding first then multiplying
• Ignoring operator precedence
• Confusing * with +

4. The formula =A1 + B1 * returns an error. What is the problem?

medium

A. Formula should start with a single quote

B. Using + instead of -

C. Missing value after the * operator

D. Parentheses are required around A1 and B1

Solution

1. Step 1: Check formula completeness

   The formula ends with an operator * but has no value after it, which is invalid.

2. Step 2: Understand Excel syntax rules

   Every operator must have values on both sides; missing value causes an error.

3. Final Answer:

   Missing value after the * operator -> Option C

4. Quick Check:

   Operators need values on both sides [OK]

Hint: Ensure every operator has values before and after [OK]

Common Mistakes:

• Leaving operator at end
• Forgetting to complete formula
• Adding unnecessary parentheses

5. You want to calculate the total price with tax in Excel. The price is in cell A2, and tax rate (as decimal) is in B2. Which formula correctly calculates price plus tax?

hard

A. =A2 + B2 * A2

B. =(A2 + B2) * A2

C. =A2 + B2

D. =A2 + B2 + A2

Solution

1. Step 1: Understand the calculation

   Total price with tax = price + (price x tax rate).

2. Step 2: Match formula to calculation

   Formula =A2 + B2 * A2 correctly adds price plus tax amount (B2 * A2).

3. Final Answer:

   =A2 + B2 * A2 -> Option A

4. Quick Check:

   Price + (TaxRate x Price) = Total [OK]

Hint: Multiply tax rate by price, then add price [OK]

Common Mistakes:

• Adding tax rate directly
• Multiplying sum incorrectly
• Using wrong operator order