๐ Excel โ Level 2: Essential Formulas
Now that you understand Excel's basic structure, the next step is learning the formulas that every Data Analyst should know.
For every function, understand:
What does it do? โ When should I use it? โ What problem does it solve?
1๏ธโฃ SUM()
SUM() adds numbers together.
Syntax
=SUM(number1, [number2], ...)
Example
Suppose:
Product Sales
Laptop 80,000
Mouse 2,000
Keyboard 5,000
To calculate total sales:
=SUM(B2:B4)
Result: 87,000
2๏ธโฃ AVERAGE()
AVERAGE() calculates the arithmetic mean.
=AVERAGE(B2:B4)
For:
80,000
2,000
5,000
the result is: 29,000
Business example
What is the average order value?
If each row represents an order:
=AVERAGE(SalesColumn)
This gives you the average sales amount per order.
3๏ธโฃ MIN()
Returns the smallest numeric value.
=MIN(B2:B100)
Example:
50,000
25,000
80,000
10,000
Result:
10,000
Common analytical uses
โข Lowest sales
โข Lowest salary
โข Minimum transaction value
โข Earliest numeric measurement
4๏ธโฃ MAX()
Returns the largest numeric value.
=MAX(B2:B100)
Example:
50,000
25,000
80,000
10,000
Result:
80,000
Common use
Find the highest sales transaction.
=MAX(SalesRange)
5๏ธโฃ COUNT()
COUNT() counts cells containing numbers.
Example:
Sales
50,000
60,000
70,000
โ
80,000
=COUNT(A2:A6)
Result:
4
The blank cell isn't counted.
COUNT() counts numeric values, not all non-empty cells.
6๏ธโฃ COUNTA()
COUNTA() counts non-empty cells.
Example:
Employee
John
Sarah
Mike
David
=COUNTA(A2:A5)
Result:
4
It can count text, numbers, dates, etc., as long as the cell isn't empty.
7๏ธโฃ COUNTBLANK()
Counts empty cells.
=COUNTBLANK(A2:A100)
This is particularly useful for data-quality checks.
Example
Suppose you have 100 customer records and 7 customers have missing email addresses.
=COUNTBLANK(EmailColumn)
Result:
7
That immediately tells you something about data completeness.
8๏ธโฃ ROUND()
Data often contains too many decimal places.
For example:
83.456789
You may want:
83.46
Use:
=ROUND(A2,2)
The 2 means two decimal places.
Examples
=ROUND(A2,0)
Rounds to a whole number.
=ROUND(A2,1)
Rounds to one decimal place.
=ROUND(A2,2)
Rounds to two decimal places.
9๏ธโฃ ROUNDUP()
ROUNDUP() always rounds away from zero.
Example:
=ROUNDUP(83.451,2)
Result:
83.46
Compare this with ROUND() where the result depends on the next digit.
This can be useful when business rules require conservative upward rounding.
๐ ROUNDDOWN()
ROUNDDOWN() always rounds toward zero.
=ROUNDDOWN(83.459,2)
Result:
83.45
Understanding the difference between:
ROUND โ ROUNDUP โ ROUNDDOWN
is useful when working with financial and operational calculations.
1๏ธโฃ1๏ธโฃ SUM vs COUNT vs AVERAGE
This is a common beginner confusion.
Suppose:
Sales:
10,000
20,000
30,000
SUM
=SUM(A2:A4)
Result:
60,000
COUNT
=COUNT(A2:A4)
Result:
3
AVERAGE
=AVERAGE(A2:A4)
Result:
20,000
Remember:
SUM โ Total
COUNT โ Number of numeric records
AVERAGE โ Mean
1๏ธโฃ2๏ธโฃ Combining Functions
The real power of Excel comes from combining functions.
For example, suppose you want:
Total sales divided by number of orders.
You could write:
=SUM(B2:B100)/COUNT(B2:B100)