Excel Math Functions

HomeExcel FunctionsExcel Math Functions

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 11 math functions that you can use in Excel. (Sample Files)

1. ABS Function

ABS Function returns a number after converting it into an absolute number. In simple words, converting a negative number into a positive and unaffecting the positive number (returns a number without a sign).

Syntax

ABS(number)

Arguments

  • number: A number or value to get a positive number of.

Notes

  • You can also use the ABS as an array function.
  • You can directly insert the number in the function.
  • If you specify a number that is not an integer, it will return #VALUE!.

Example

In the below example, we have used ABS to convert negative values into positive ones. It has just removed the negative sign from the numbers.

excel-abs-function-example-1

And here we have used ABS to get absolute values against the change in the interest rate.

excel-abs-function-example-2

2. EVEN Function

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 round down.

Syntax

ISEVEN(number)

Arguments

  • number: The value which you want to evaluate.

Notes

  • You can also insert a number into 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:

  1. When you refer to a negative value, it will always round it to lower even number, away from zero.
  2. When you refer to a positive value, it will always round it to the upper even number.
  3. And when you refer to zero it will remain the same.
excel-even-function-example-1

3. INT Function

INT function returns the integer part of a decimal number. In simple words, it rounds down the number by removing its decimals and returns only the integer part of the number.

Syntax

INT(number)

Arguments

  • number: Number you want to round down to the nearest integer.

Notes

  • It will round down both positive or negative numbers.

Example

In the below example, we have rounded 9.9 to 9 and 8.9 to 8. You can use this rounding function to remove decimals from the numbers.

excel-int-function-example-1

4. MOD Function

MOD function returns the remainder value after dividing a number with a divisor. In simple words, it returns that value which remains after the division of two numbers.

Syntax

MOD(number, divisor)

Arguments

  • number: The number which you want to divide.
  • divisor: The number with which you want to divide

Notes

  • It will return a #DIV/0! error if the divisor is zero.

Example

In the below example, we have used MOD with the different types of values:

excel-mod-function-example-1
  1. If there is no remainder after dividing two numbers it returns zero.
  2. If there is a decimal in divisor it considers that decimal and returns result according to that.
  3. If you specify zero to divide, it returns a 0 in the result. Dividing any number by zero results in zero.
  4. If the divisor will zero, it will return #DIV/0!.
  5. If you skip specifying any number for dividing it will return 0 in the result.
  6. And if you skip specifying any divisor, it will return #DIV/0!.
  7. It will consider negative values and return the same sign that the divisor has.

5. MROUND Function

MROUND function returns a number after rounding it to a given multiple. In simple words, it rounds a number to the nearest multiple of a number and while rounding, it doesn’t consider that number is greater or lower than the original number.

Syntax

MROUND(number, multiple)

Arguments

  • number: A number to round.
  • multiple: A number for which you want to find the nearest multiple.

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

In the below example, we have used different arguments:

excel-mround-function-example-1
  1. 12.5 rounded to 12 to the nearest multiple of 3 is 12.
  2. 19 is rounded to 20 to the nearest multiple of 5
  3. 13 is rounded to 12 to the nearest multiple of 6.
  4. 3.6 is rounded to 2 to the nearest multiple of 4.
  5. The result is 0 as I have skipped specifying multiple.
  6. The result is 0 as I have specified 0 as the multiple.
  7. The result is an error as I have specified -2 as multiple.
  8. Here we have a tie between 20 and 18, but we get 20 as it always rounds away from zero.

6. RAND Function

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.

excel-rand-function-example-1

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:

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

Related: How to Quickly Generate Random Letters in Excel

7. 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.

excel-sum-function-example-1

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.

excel-sum-function-example-2

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

excel-sum-function-example-3

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.

excel-sum-function-example-4

8. 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 range will be summed.
  • Make sure to use double quotation marks to specify Text criteria or criteria that include math symbols, 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 criteria range and B1:B9 as sum range and after that, we have specified the criteria in A12 which has the value C.

excel-sumif-function-example-1

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

excel-sumif-function-example-2

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.

excel-sumif-function-example-3

9. SUMIFS Function

SUMIFS Function returns the sum of the numbers which meet multiple conditions you specify. Unlike SUMIF, you can set multiple conditions and can sum only those values which meet all those conditions.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

  • [sum_range]: A range of cells & values which you want to sum.
  • criteria_range1: A range from which you want to test criteria.
  • criteria1: A criteria which can be a number, text, expression, cell reference or a function.
  • [criteria_range2]: A range from which you want to test criteria.
  • [criteria2]: A criteria which can be a number, text, expression, cell reference or a function.

Notes

  • The size of the criteria range and sum range should be of the same size.
  • You can also use wildcard characters.
  • If you want to sum values based on single criteria, you can use SUMIF.
  • If you want to specify criteria in the form of text, you have to use double quotation marks.
  • If you skip specifying SUM range it will SUM the values from the criteria range that meet the criteria.

Example

In the below example, we have used three different criteria to check and it has returned the sum of the values which meet those criteria.

excel-sumifs-function-example-1

In the below example, we have used wildcard characters to specify three different criteria.

excel-sumifs-function-example-2
  • The first criteria range is A2:A9 and criteria in the cell A12 which will only sum cells that have alphabet O.
  • The second criterion range is B2:B9 and criteria in cell B12 which will only sum cells that have alphabet P. It will apply to all the cells which met condition 1.
  • The third criterion range is C2:C9 and criterion in cell C12 which will sum cells with any type of value. It will apply to all the cells which met the condition 1 and 2.

10. SUMPRODUCT Function

SUMPRODUCT Function returns a value after sum and multiplies values from the ranges or arrays. In simple words, it first multiplies the corresponding cells from ranges and then sums up all the values.

Syntax

SUMPRODUCT(array1, [array2], [array3], …)

Arguments

  • array1: The first array you want to multiply and then add.
  • [array2]: The second array you want to multiply and then add.

Notes

  • If skip you to specify array2, SUMPRODUCT will simply sum the array1.
  • The maximum size of each array should be the same. If array1 has 5 cells then cells in array2 should be 5.
  • Text and other non-numeric entries will be treated as 0.

Example

In the below example, we have used SUMPRODUCT to multiply and sum up the values from column D and column F. First, it has multiplied the values from column D with column F and then sums up the values.

excel-sumproduct-function-example-1

Related: Conditional Ranking in Excel using SUMPRODUCT Function [RANKIF] / How to use SUMPRODUCT IF to Create a Conditional Formula in Excel

11. 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.

excel-trunc-function-example-1

More Excel Functions

Excel Date Functions | Excel Time Functions | Excel Information Functions | Excel String (Text) Functions | Excel Logical Functions | Excel Statistical Functions | Excel LookUp Functions | Excel Financial Functions