MATHEMATICAL FUNCTIONS
In excel
Group Members
1. Rutendo T Karimupfumbi R2424630 HDSC
2. Tendai Mutyavaviri R2424709 HASTS
3. Chidochashe Gwenzi R2418445 HDSC
4. Emmanuel T Charakupa R2418472 HDSC
5. Nondumiso Saungweme R2419451 HASTS
6. Blessed Mavhemwa R2424219 HDSC
7. Panashe Goboza R2419440 HDSC
8. Hebert A Mugombi R234735Z HDSC
Title: Mathematical Functions in
Excel
Objectives:
• Learn key Excel math functions
• Apply them to real datasets
• Practice with hands‑on examples
Why Excel for Math?
Automates repetitive calculations
Handles large datasets easily
Integrates with charts and dashboards
Example: =SUM(A1:A12) → Monthly sales totals
Common Errors
1. Error
Cause: Column too narrow to display the value, or negative date/time values.
Fix: Widen the column or check the data format.
2. DIV/0
Cause: Division by zero or by an empty cell
Example: =A1/0 → DIV/0
Fix: Use =IF(B1=0,"N/A",A1/B1) to avoid dividing by zero.
3. VALUE
Cause: Wrong data type in a formula (e.g., text instead of numbers).
Example: =10+"Ten" → VALUE
Fix: Ensure all inputs are numeric or convert text using VALUE()
.4. NAME
Cause: Misspelled function name or undefined named range.
Example: =SUUM(A1:A5) → NAME
Fix: Correct spelling (=SUM(A1:A5)) or define the range.
Common Errors
5. REF
Cause: Invalid cell reference, often due to deleting referenced cells.
Example: If =A1+B1 and column A is deleted → REF
Fix: Adjust formulas to valid references
.6. NUM
Cause: Invalid numeric calculation.
Example: =SQRT(-16) → NUM
Fix: Check inputs; Excel doesn’t handle imaginary numbers.
7. N/A
Cause: Lookup functions (like VLOOKUP) can’t find a match.
Example: =VLOOKUP("X",A1:B10,2,FALSE) → N/A if “X” isn’t found.
Fix: Ensure lookup values exist or use IFERROR() to handle missing data.
8. Circular Reference
Cause: A formula refers to its own cell directly or indirectly.
Example: In cell A1: =A1+[Link]: Redesign formulas to avoid self‑reference
Categories of Functions
Basic arithmetic → SUM, PRODUCT
Rounding & absolute → ROUND, ABS
Statistical basics → AVERAGE, COUNT, MIN, MAX
Advanced math → POWER, SQRT, LOG, TRIG
Basic Arithmetic
These are simple operations you can perform using formulas or functions
Eg . B4=10
A4=5
Multiplication: =B4*A4
Addition : There're two ways to add values in Excel, that is, direct addition and
SUM()
Direct addition:=B4+A4
SUM:=SUM(B4:A4)
Division:=B4/A4
Avoid dividing by zero, lest you don't get the desired results
Subtraction:=B4-A4
Rounding & Absolute
1. ROUND
Formula: =ROUND(number, num_digits)
Use: Rounds a number to a specific number of decimal places.
Example: - In cell A1 type 23.7825
In cell B1 type =ROUND(A1,2) → Result: 23.78
In cell C1 type =ROUND(A1,0) → Result: 24
2. ROUNDUP
Formula: =ROUNDUP(number, num_digits)
Use: Always rounds up (away from zero).
Example: =ROUNDUP(23.7825,2) → 23.79
=ROUNDUP(1234.56,-2) → 1300
3. ROUNDDOWN
Formula: =ROUNDDOWN(number, num_digits)
Use: Always rounds *down* (toward zero).
Example: =ROUNDDOWN(23.7825,2) → 23.78
=ROUNDDOWN(1234.56,-2) → 1200
Rounding & Absolute
4. CEILING
Formula: =CEILING(number, significance)
Use: Rounds up to the nearest multiple of a given number.
Example: =CEILING(23,5) → 25
=CEILING(23.7825,0.1) → 23.8
5. FLOOR
Formula: =FLOOR(number, significance)
Use: Rounds down to the nearest multiple of a given number.
Example: =FLOOR(23,5) → 20
=FLOOR(23.7825,0.1) → 23.7
6. MROUND
Formula: =MROUND(number, multiple)
Use: Rounds to the *nearest multiple* of a given number.
Example: =MROUND(23,5) → 25 =MROUND(22,5) → 20
Logarithms
1. Natural Log
(base e)=LN(10)
This gives the natural logarithm of 10 (base e ≈ 2.718).
2. Log base 10
Use LOG10(number) =LOG10(100) which returns 2
3. Log with ANY base
Use LOG(number, base).=LOG(8, 2) which returns 3
This gives log₂(100
Exponential
1. e^x
Use EXP()
=EXP(2)
This calculates e².2.
General exponential a^b
Use the caret symbol ^.=2^5 which returns 32
Mathematical functions
Mathematical function Use Example
Average of a range It shows the average of a =AVERAGE(A1:A10)
range
Median of a range It shows the central value =MEDIAN(A1:A10)
when numbers are arranged
from smallest to largest
Mode It shows the most frequently =[Link](A1:A10)
occurring number
Minimum It returns the smallest =MIN(A1:A10)
number in a range
Absolute It changes any number to a =ABS(A1)
positive version
Square Root It finds a number which when =SQRT(A1)
multiplied by itself gives the
original number
Power Raises a number to certain =POWER(A3,3)
exponent
Product It multiplies numbers in a
range =PRODUCT(A1:A10)
Maximum It returns the biggest number =MAX(A1:A10)
Random Numbers
1. RAND() – Random Decimal Numbers
What it does: Creates a decimal between 0 and 1.
Formula: =RAND()
Example outputs: 0.247389, 0.983142, 0.455720
Example (decimal between 10 and 20):=RAND()*(20-10)+10
2. RANDBETWEEN() – Random Whole Numbers
What it does: Creates a whole number between two values.
Formula: =RANDBETWEEN(bottom, top)
Example: =RANDBETWEEN(1,100) → 45, 98, 12, 67
Example (random age 18–30):=RANDBETWEEN(18,30)
3. RANDARRAY() – Many Random Numbers at Once
What it does: Generates multiple random numbers.
Formula: =RANDARRAY(rows, columns, min, max, whole)
Example 1: =RANDARRAY(5,1) → 5 decimals
Example 2: =RANDARRAY(10,1,10,50,TRUE) → 10 integers (10–50)
Statistical functions
- AVERAGE:
=AVERAGE(range) - calculates the average of values in a range
- MEDIAN:
=MEDIAN(range) - calculates the middle value of values in a range
- MODE:
=MODE(range) - calculates the most frequently occurring value in a range
- STDEV:
=STDEV(range) - calculates the standard deviation of values in a range
- VAR:
=VAR(range) - calculates the variance of values in a range
Trigonometry functions
Basic Trig Functions
SIN(number) → Returns the sine of an angle (in radians).
Example: =SIN(PI()/2) → 1
COS(number) → Returns the cosine of an angle.
Example: =COS(PI()) → -1
TAN(number) → Returns the tangent of an angle.
Example: =TAN(PI()/4) → 1
Combining Functions
Nested Example:
Code=ROUND(AVERAGE(B2:B10),2)
Average sales rounded to 2 decimals
Explanation: Shows how functions can be combined for precision.
Key functions and their uses
Function Syntax Use Case Example
Adds numbers in a
SUM =SUM(A1:A10) Total monthly sales
range
Quantity × Price ×
PRODUCT =PRODUCT(A1:A3) Multiplies values
Tax
Rounds to specified Round financial
ROUND =ROUND(3.14159,2)
decimals figures
ROUNDUP /
=ROUNDUP(45.67,0) Rounds up/down Invoice adjustments
ROUNDDOWN
Returns absolute
ABS =ABS(-25) Distance or deviation
value
AVERAGE =AVERAGE(B2:B12) Finds mean of values Class exam average
Counts numeric Number of students
COUNT =COUNT(B2:B12)
entries with scores
=MIN(B2:B12) / Finds smallest/largest Lowest & highest
MIN / MAX
=MAX(B2:B12) value marks
Square or cube
POWER =POWER(5,2) Raises to a power
calculations
Geometry or science
SQRT =SQRT(144) Square root
problems
LOG =LOG(100,10) Logarithm with base Growth rate analysis
TRIG (SIN, COS, Trigonometric Engineering
=SIN(PI()/2)
TAN) functions calculations
Conclusion
Excel provides a powerful set of mathematical functions for everyday tasks, from simple
addition to advanced scientific calculations.
Key functions like SUM, AVERAGE, ROUND, ABS, MIN, MAX, POWER, SQRT, and LOG help
automate and simplify analysis .
Using these functions correctly reduces errors, saves time, and improves accuracy in
reports .
Common errors (#DIV/0!, #VALUE!, #NAME?, #REF!, #NUM!) highlight the importance
of clean data and careful formula design.
By combining formulas with charts and case studies, Excel becomes not just a calculator
but a decision‑making tool.
Mastery of these functions builds confidence in handling datasets across business,
education, and research.
Final takeaway: Excel isn’t just about numbers—it’s about turning raw data into clear
insights.
Questions?
THANK YOU