Mathematical Functions in Excel – Part 1

By /
Mathematical Functions in Excel Part I

In this article, we’ll learn the mathematical functions in Excel. One may need to carry out basic mathematical calculations in Excel very often, to facilitate the process MS Excel provides inbuilt functions to deliver fast and accurate results.

Top Mathematical Functions in Excel

Here are few commonly used mathematical functions in Excel.

1. ABS function

The ABS function in Excel returns the absolute value or the modulus of any given number. In other words, transforming a negative number into a positive while leaving the positive number alone (returns a number without a sign).

Syntax: ABS(number), where number is the number for which you want the modulus or the cell reference of the cell containing the number. The following example shows the use of ABS function:

  • Select the cell where you want to display the result.
  • Type =ABS(, select the cell containing the number and complete the formula with ).
abs formula
  • Press the Enter key to display the result.
abs result

2. SIGN function

The SIGN function in Excel returns the arithmetic sign +1, -1, or 0 of an integer. In other words, if the number is positive, the SIGN function returns +1, if it is negative, the function returns -1, and if it is zero, the function returns 0.

Syntax: SIGN(number), where number is the number for which you want the sign or the cell reference of the cell containing the number. The following example shows the use of SIGN function:

  • Select the cell where you want to display the result.
  • Type =SIGN(, select the cell containing the number and complete the formula with ).
sign formula
  • Press the Enter key to display the result.
sign result

3. GCD function

The GCD function in Excel returns the greatest common divisor of two or more numbers. Syntax: GCD(number1,number2,…), where number1, number2, etc. are the numbers for which you want the GCD or the cell references of the cells containing the numbers. The following example shows the use of GCD function:

  • Select the cell where you want to display the result.
  • Type =GCD(A1,B1), where cells A1 and B1 contain the first and second numbers respectively.
gcd formula
  • Press the Enter key to display the result.
gcd result

Note:

  • The GCD function returns the #NUM! error value if any of the arguments is less than zero.
  • If a parameter to the GCD function is greater than or equal to 2^53, GCD returns the #NUM! error value.

4. LCM function

The LCM function in Excel returns the least common multiple of two or more integers.

Syntax: LCM(number1,number2,…), where number1, number2, etc. are the numbers for which you want the LCM or the cell references of the cells containing the numbers. The following example shows the use of LCM function:

  • Select the cell where you want to display the result.
  • Type LCM(A1,B1), where cells A1 and B1 contain the first and second numbers respectively.
lcm formula
  • Press the Enter key to display the result.
lcm result

Note:

  • The LCM function returns the #NUM! error value if any of the arguments is less than zero.
  • If a parameter to the LCM function is greater than or equal to 2^53, LCM returns the #NUM! error value.

5. QUOTIENT function

The QUOTIENT function in Excel returns the integer component of a division between two values.

Syntax: QUOTIENT(numerator,denominator), where numerator is the number to be divided and denominator is the number that divides the numerator. The following example shows the use of QUOTIENT function:

  • Select the cell where you want to display the result.
  • Type QUOTIENT(A1,B1), where cells A1 and B1 contain the first and second numbers respectively.
quotient formula
  • Press the Enter key to display the result.
quotient result

6. MOD function

The MOD function in Excel returns the remainder of a division between two values.

Syntax: MOD(numerator,denominator), where numerator is the number to be divided and denominator is the number that divides the numerator. The following example shows the use of MOD function:

  • Select the cell where you want to display the result.
  • Type MOD(A1,B1), where cells A1 and B1 contain the first and second numbers respectively.
mod formula
  • Press the Enter key to display the result.
mod result

Note: The MOD function returns the #DIV/0! error value if the denominator is zero.

7. FACT function

The FACT function in Excel returns the factorial of a given integer. The factorial of a number is given by: n! = n * (n-1) * … * 2 * 1.

Syntax: FACT(number), where number is the number for which you want the factorial or the cell reference of the cell containing the number. The following example shows the use of FACT function:

  • Select the cell where you want to display the result.
  • Type =FACT(, select the cell containing the number and complete the formula with ).
fact formula
  • Press the Enter key to display the result.
fact result

Note: The FACT function returns a #NUM! error value for negative integers.

Conclusion

In this article, we learned about some of the most commonly used mathematical functions in Excel.

References