Or simply:
=AVERAGE(B2:B100)
Understanding both approaches helps you understand what Excel is actually calculating.
1️⃣3️⃣ Using Cell References Instead of Hardcoding
Avoid unnecessary hardcoding.
Instead of:
=SUM(B2:B100)_1.18
you could put the tax rate in another cell.
For example:
F1 = 18%
Then:
=SUM(B2:B100)_(1+$F$1)
Now if the tax rate changes, you only change F1.
This makes your analysis more flexible.
1️⃣4️⃣ Relative References
Consider:
=B2_C2
If you copy this formula to row 3, Excel changes it to:
=B3_C3
This is a relative reference.
It's extremely useful when applying the same calculation to many rows.
1️⃣5️⃣ Absolute References
Suppose:
F1 = 18%
You want to apply this percentage to every row.
Use:
=C2_$F$1
When copied down:
=C3_$F$1
=C4_$F$1
=C5_$F$1
F1 stays fixed.
The $ tells Excel:
Don't move this reference.
1️⃣6️⃣ Mixed References
You may also encounter:
$A1
A$1
$A1
Column A is fixed, row can change.
A$1
Row 1 is fixed, column can change.
These become particularly useful when building complex Excel models.
🧪 Practical Example
Suppose you have:
Employee Sales
John 50,000
Sarah 75,000
Mike 60,000
David 90,000
Alice 45,000
You can calculate:
Total Sales
=SUM(B2:B6)
320,000
Average Sales
=AVERAGE(B2:B6)
64,000
Highest Sales
=MAX(B2:B6)
90,000
Lowest Sales
=MIN(B2:B6)
45,000
Number of Employees
=COUNT(B2:B6)
5
🎯 Mini Interview Challenge
Your interviewer gives you this dataset:
Employee Sales
John 45,000
Sarah 80,000
Mike 65,000
David 95,000
Alice 55,000
They ask:
Q1. What is total sales?
=SUM(B2:B6)
Q2. What is average sales?
=AVERAGE(B2:B6)
Q3. What is the highest sales?
=MAX(B2:B6)
Q4. What is the lowest sales?
=MIN(B2:B6)
Q5. How many employees have sales values?
=COUNT(B2:B6)
If you can answer these comfortably, you've covered the core of Excel Level 2.
🏆 Quick Recap
"What is the total?" → SUM()
"What is the average?" → AVERAGE()
"What is the highest?" → MAX()
"What is the lowest?" → MIN()
"How many numeric records?" → COUNT()
"How many non-empty records?" → COUNTA()
"How many missing values?" → COUNTBLANK()
Double Tap ❤️ For Part-4