Excel’s Math Functions can be used to perform common mathematical operations and analyze the data in spread sheet. For example, they can be used to find the average value of data and number of data entries etc.
Finding the right MATH function in excel 2010.
Follow the below steps to identify the math function in excel.
Open excel work sheet
Click the Formulas tab in excel and then click the insert function.
Select the category as Math and Trig and you can see all the Math functions.
As a result, Excel’s Math function appears in alphabetic order in the select a function box. By this we can find the right Math function 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
The SUM function adds range of numbers and return the sum of these values.
Formula for the function is
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
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 function returns to the result of the number raised to a given power.
Formula for POWER function is
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 return the positive square root of the given function.
Formula for SQRT is
Below screen shot shows example of SQRT function.
Note: This function returns #NUM! error if the number is negative
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
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
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.
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
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.
This function returns the absolute value of the number i.e. a number without its sign.
Formula for ABS function is
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.
This function is used to add the cells specified by a given condition or criteria.
Formula for SUMIF function is
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.
This function round the number to a specified number if digits.
Formula for the round function is
Num_digits implies the number of decimal places to round to.
Below screen shot shows the example of ROUND function.
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