Math functions in Excel 2010

I am going to show you the essential built-in Excel Math functions, grouped into categories with simple examples.

MATH FUNCTION EXAMPLES

• SUM
• PRODUCT
• POWER
• SQRT
• SUMPRODUCT
• ODD
• EVEN
• ABS
• SUMIF
• ROUND

SUM:

The SUM function adds range of numbers and return the sum of these values.

Formula for the function is

=sum(number1, (number2)…)

Number implies the set of numbers that you want to find the sum of.

Below screen shot shows the example of SUM function

Note:Error of Excel sum function is likely to be #Value! error

PRODUCT:

This function returns the product of range of numbers selected.

Formula for the function is

=product(number1, (number2), …)

Number implies the set of numbers that you want to find the product of.

Below screen shot shows the product of values in different range of cells.

Note: If there is error in PRODUCT function, it is likely to be the value error.

POWER:

POWER function returns to the result of the number raised to a given power.

Formula for POWER function is

=POWER(number,power)

Both number and power values must be numerical. Number is the base number and power is the exponent used to raise the base number.

Above screen shot shows the example of Power function.

SQRT:

SQRT return the positive square root of the given function.

Formula for SQRT is

=SQRT(number)

Below screen shot shows example of SQRT function.

Note: This function returns #NUM! error if the number is negative

SUMPRODUCT:

This function returns the sum of the products of corresponding ranges or arrays.

Formula for SUMPRODUCT is

=SUMPRODUCT(array1, array2, array3….).

The Array values may be one or more cells or ranges of cell containing numeric values. The non-numeric values in the array ranges are treated as the value zero.

Below screenshot shows the example of SUMPRODUCT function with Array1, Array2 and Array3

Note: The Array argument must have the same dimension. This function returns #NUM! error if they do not SUMPRODUCT

ODD:

This function rounds the number to next odd number i.e. positive number up and negative number down to the nearest odd integer.

Formula for ODD function is

=ODD(number)

Below screen shot shows the example of ODD function

Note: ODD function always rounds away from Zero to next odd number. If the number is odd it returns the same odd number.

EVEN:

This function rounds the number to next even number i.e. positive number up and negative number down to the nearest even integer.

Formula for EVEN function is

=EVEN(number)

Below screen shot shows the example of EVEN function

Note: EVEN function always rounds away from Zero to next Even number. If the number is Even, it returns to same Even number.

ABS:

This function returns the absolute value of the number i.e. a number without its sign.

Formula for ABS function is

=ABS(number)

Number is the numeric value which can be either simple number or a reference to the cell containing the number.

Below screen shot shows the example of ABS function

Note: The error in the ABS function will be  #VALUE! error.

SUMIF:

This function is used to add the cells specified by a given condition or criteria.

Formula for SUMIF function is

=SUMIF(range,criteria,(sum_range))

Range implies range of cells containing the values

Criteria implies condition to be tested against each of the values in range

(sum_range) implies the cells containing the numbers which are to be added together if the range satisfies the criteria.

Below screen shot shows the example of SUMIF function.

Note: If the criteria is Text or an expression, this must be supplied to the function in quotes. Also SUMIF function is non-case sensitive.

ROUND:

This function round the number to a specified number if digits.

Formula for the round function is

=ROUND(number,num_digits)

Num_digits implies the number of decimal places to round to.

Below screen shot shows the example of ROUND function.

Note:

Positive num_digit value specifies the number of digit to the right of the decimal point

Negative num_digit value specifies the number of digit to the left of the decimal point