Excel’s Top 5 Math Functions (Examples + Sample Files)

Contents hide

In Excel, there are multiple math functions that can help you to get the mathematical calculations done, and in this post, we have covered the top 5 math functions that you can use in Excel.

(Sample Files)

1. SUM Function

SUM function returns the sum of the values supplied. In simple words, with the SUM function, you can calculate the sum of a list of values (you can directly input value into the function or refer to a range of cells.

Syntax

SUM(number1,[number2],…)

Arguments

number1: A number, a range of cells that contain numbers, or a single cell that contains a number.
[number2]: A number, a range of cells that contain numbers, or a single cell that contains a number.

Notes

It ignores text values.

Example

In the below example, you can insert numbers directly into the function by using commas between them.

You can also simply refer to a range for calculating the sum of the numbers and if there is a text, logical value, or empty cell it will ignore them.

If there is an error value in a cell that you are referring to, it will return #N/A in the result.

If you have numeric values that are formatted as the text it will ignore them. It’s recommended to convert them into numbers before using SUM.

2. SUMIF Function

SUMIF Function returns the sum of the numbers which meet the condition you specify. In simple words, it only considers and calculates the sum of values that fulfill the condition.

Syntax

SUMIF(range, criteria, [sum_range])

Arguments

range: A range of cells from which you want to check for criteria.
criteria: A criteria which can be a number, text, expression, cell reference or a function.
[sum_range]: A cell range that has the values you want to sum.

Notes

If the sum_range is omitted, the cells in the range will be summed.
Make sure to use double quotation marks to specify Text criteria or criteria that include math symbols, which must be enclosed in double quotation marks.
The size of the criteria range and sum range should be of the same size.

Example

In the below example, we have specified A1:A9 as the criteria range and B1:B9 as the sum range and after that, we have specified the criteria in A12 which has the value C.

You can also insert criteria directly into the function. In the below example, we have used an asterisk wildcard to specify a criterion that has an alphabet “S”.

And, if you skip specifying the sum range it will give you the sum of the criteria range. But, that will be only possible if the criteria range has numeric values.

3. EVEN Function

The EVEN function rounds a number to the nearest even number. If the specified number is positive, then it rounds up and if that number is negative then rounds down.

Syntax

ISEVEN(number)

Arguments

number: The value which you want to evaluate.

Notes

You can also insert a number into a function directly by using double quotes or even without it.
If you specify a non-numeric value it will return the #VALUE! error value.

Example

We need to try it out in an example, so make sure to check out the below one: Here we have used different arguments:

When you refer to a negative value, it will always round it to a lower even number, away from zero.
When you refer to a positive value, it will always round it to the upper even number.
And when you refer to zero it will remain the same.

4. RAND Function

The RAND function returns a random number ranging from 0 to 1. In simple words, you can generate a random number between 0 to 1 (it updates its value every time you make a change in the worksheet).

Syntax

RAND()

Arguments

there is no argument to specify in RAND functions

Notes

If you put zero in multiple it will return zero in the result.
If you skip specifying multiple it will return with an error.
It rounds away from zero.
If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.

Example

Apart from having numbers between 0 and 1, you can also use RAND for random numbers between two specific numbers. In the below example, I have used it to create a formula that generates a random number between 50 and 100.

When you enter this formula in a cell it returns a number between 100 and 50 by multiplying the values returned by the RAND with the equation we have used. To understand this formula we need to split it into three parts:

First of all, when it detects the lowest number from the highest number you get the difference between both of them.
Then secondly it multiplies that difference with the random number returned after the subtraction.
And third, add that number with the lowest number left in the third part of the equation.

5. TRUNC Function

TRUNC Function returns an integer after truncating the original number. In simple words, it removes the decimals from a number to a specific precision and then returns the integer part of the result.

Syntax

TRUNC(number, [num_digits])

Arguments

number: The number you want to truncate.
[num_digits]: A number to specify precision to truncate a number.

Notes

If you skip specifying multiple it will return with an error.
It rounds away from zero.
If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.

Example

In the below example, we have used TRUNC to truncate data for removing time from the dates.

More Excel Functions