Why formulas automate calculations in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a simple sales report that automatically calculates total sales and average sales per day.

📊 Data: You have daily sales data for 10 days, including the date and the amount sold each day.

🎯 Deliverable: Create a spreadsheet that uses formulas to automatically calculate the total sales and the average sales per day.

Sample Data

Date	Sales Amount
2024-06-01	150
2024-06-02	200
2024-06-03	180
2024-06-04	220
2024-06-05	170
2024-06-06	210
2024-06-07	190
2024-06-08	230
2024-06-09	160
2024-06-10	200

1

Step 1: Enter the daily sales data into two columns: 'Date' in column A and 'Sales Amount' in column B, starting from row 2.

Expected Result

Data is organized with dates in A2:A11 and sales amounts in B2:B11.

2

Step 2: In cell B12, calculate the total sales using a formula that adds all sales amounts.

=SUM(B2:B11)

Expected Result

Total sales amount is 1910.

3

Step 3: In cell B13, calculate the average sales per day using a formula that finds the average of the sales amounts.

=AVERAGE(B2:B11)

Expected Result

Average sales per day is 191.

4

Step 4: Label cells A12 as 'Total Sales' and A13 as 'Average Sales per Day' for clarity.

Expected Result

Cells A12 and A13 show descriptive labels.

5

Step 5: Test the formulas by changing one sales amount in the data and observe that total and average update automatically.

Change B2 from 150 to 160

Expected Result

Total sales updates to 1920 and average sales updates to 192.

Final Result

Date       | Sales Amount
-------------------------
2024-06-01 | 150
2024-06-02 | 200
2024-06-03 | 180
2024-06-04 | 220
2024-06-05 | 170
2024-06-06 | 210
2024-06-07 | 190
2024-06-08 | 230
2024-06-09 | 160
2024-06-10 | 200
-------------------------
Total Sales          1910
Average Sales per Day 191

✓Formulas like SUM and AVERAGE automatically update results when data changes.

✓This automation saves time and reduces errors compared to manual calculations.

✓Using formulas helps keep reports accurate and up-to-date easily.

Bonus Challenge

Add a formula to calculate the highest sales day and display the date and amount.

Show Hint

Use MAX to find the highest sales amount and MATCH or INDEX to find the corresponding date.

Practice

1. Why do formulas in Excel start with an = sign?

easy

A. To tell Excel to calculate the expression

B. To make the cell bold

C. To add a comment in the cell

D. To change the cell color

Solution

1. Step 1: Understand the role of = in Excel

   The = sign tells Excel that the cell contains a formula, not just text or a number.

2. Step 2: Recognize what happens without =

   Without =, Excel treats the input as plain text or number and does not calculate anything.

3. Final Answer:

   To tell Excel to calculate the expression -> Option A

4. Quick Check:

   Formulas start with = to calculate [OK]

Hint: Remember: = means 'calculate this' in Excel [OK]

Common Mistakes:

• Thinking = changes formatting
• Confusing = with comments
• Believing = adds colors

2. Which of these is the correct way to write a formula that adds values in cells A1 and B1?

easy

A. =A1+B1

B. A1+B1

C. =SUM(A1 B1)

D. SUM=A1+B1

Solution

1. Step 1: Check formula syntax for addition

   The correct formula must start with = and use + to add cells: =A1+B1.

2. Step 2: Identify errors in other options

   A1+B1 misses =, =SUM(A1 B1) has wrong syntax for SUM, SUM=A1+B1 misplaces = sign.

3. Final Answer:

   =A1+B1 -> Option A

4. Quick Check:

   Correct formula syntax =A1+B1 [OK]

Hint: Always start formulas with = and use + for addition [OK]

Common Mistakes:

• Omitting the = sign
• Using wrong function syntax
• Placing = inside function name

3. If cell A1 contains 5 and B1 contains 3, what will be the result of the formula =A1*B1?

medium

A. 8

B. Error

C. 53

D. 15

Solution

1. Step 1: Identify the operation in the formula

   The formula uses * which means multiplication.

2. Step 2: Multiply the values in A1 and B1

   5 multiplied by 3 equals 15.

3. Final Answer:

   15 -> Option D

4. Quick Check:

   5 * 3 = 15 [OK]

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

Common Mistakes:

• Adding instead of multiplying
• Concatenating numbers as text
• Expecting a syntax error

4. A user wrote the formula =SUM(A1:A5 but it shows an error. What is the likely problem?

medium

A. Wrong function name

B. Missing closing parenthesis

C. Cells A1 to A5 are empty

D. Formula should start with + instead of =

Solution

1. Step 1: Check formula syntax for SUM function

   The SUM function requires parentheses around the range, so it must be =SUM(A1:A5).

2. Step 2: Identify missing syntax element

   The formula is missing the closing parenthesis, causing a syntax error.

3. Final Answer:

   Missing closing parenthesis -> Option B

4. Quick Check:

   SUM needs matching parentheses [OK]

Hint: Always close parentheses in functions [OK]

Common Mistakes:

• Forgetting closing parenthesis
• Using + instead of =
• Assuming empty cells cause errors

5. You want to calculate the total price by multiplying quantity in B2 by price per item in C2. Which formula will update automatically if you change quantity or price?

hard

A. =SUM(B2,C2)

B. B2*C2

C. =B2*C2

D. =B2+C2

Solution

1. Step 1: Choose formula that multiplies quantity and price

   The correct formula multiplies B2 and C2 using =B2*C2.

2. Step 2: Confirm formula updates automatically

   Formulas starting with = recalculate when referenced cells change, so total updates automatically.

3. Final Answer:

   =B2*C2 -> Option C

4. Quick Check:

   Formula with = and * updates automatically [OK]

Hint: Use = and cell references to auto-update calculations [OK]

Common Mistakes:

• Omitting = so no calculation happens
• Using SUM instead of multiplication
• Adding instead of multiplying