Good to know mathematical functions in Excel ?

1. AVERAGE
Formula:
=AVERAGE(A1:A9)

Average formula returns the average (arithmetic mean) of the selected data. For example, if the data range A1:A9 contains numbers, the formula =AVERAGE(A1:A9) returns the average of those numbers.

Description:
AVERAGE(number1, [number2], ...)

Number1  is required. The first number, cell reference, or range for which you want the average.
Number2, is  optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.
Average is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers.

Example:
The average of 2, 7, 4, 8, 6, and 9 is 36 divided by 6, which is 6.

2. INT
Formula:
=INT(number)

Rounds a number down to the nearest integer. i.e. this function converts the decimal number to the integer lower than it.

Example :
The formula =INT(9.234) will give output as 9

Note:
INT will not roundoff the number but converts the decimal number to lowest round number. e.g. =INT(9.564) will also give output as 9


3. MOD
Formula:
=MOD(Number, Divisor)

This function returns the remainder after number is divided by divisor. The result of this function has the same sign as divisor.

Note:
If divisor is 0, then the MOD function returns the #DIV/0! error value.

Example :
=MOD(6,9) will give output as 6
=MOD(-6,-9) will give output as -6, as mentioned above, the output sign remains same as divisor sign


4. RAND
Formula:
=RAND()

The RAND function returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. You will get a one random number for once and the formula will be replaced by the number.

Example:
The formula =RAND()*90 will give output with a random number greater than or equal to 0 but less than 90. You can change the 90 in this formula, to the number you want.


5. ROUND
Formula:
=ROUND(A1, 2)

The ROUND function rounds a number to a specified number of digits.

Example:
If cell A1 contains 99.5682, and you want to round that value to two decimal places, you can use the above formula. The output of this function will be 99.57

Further if you use the formula =ROUND(A1, 0), the output of this function will be 100.

Description:
ROUND(number, num_digits)
number  is required. Enter the number that you want to round.
num_digits  is required. The number of digits to which you want to round the number.
If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
If num_digits is 0, the number is rounded to the nearest integer.
If num_digits is less than 0, the number is rounded to the left of the decimal point.


6. EVEN
Formula:
=Even(number)

The EVEN function returns the number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing box accepts rows of only one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the box capacity.

If number is nonnumeric, EVEN returns the #VALUE! error value.
Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an even integer, no rounding occurs.

Example:
The formula =EVEN(3) will give output as 4, as it is the nearest even integer value.
The formula =EVEN(-1) will give output as -1, as it is the nearest even integer value on the negative side.


7. MROUND
Formula:
=MROUND(number, multiple)

The MROUND function returns the number rounded to the desired multiple value.

MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.

Example:
The formula =MROUND(9,4) will give the output as 8, which is the nearest multiple of 4.
The formula =MROUND(9.9,3.2) will give the output as 9.6, which is the nearest multiple of 3.2.


8. ODD
Formula:
=ODD(number)

The ODD function, returns the number rounded up to the nearest odd integer.

If number is nonnumeric, ODD returns the #VALUE! error value.
Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an odd integer, no rounding occurs.
Example:
The formula =ODD(4) will give the output as 5, which is the nearest odd number.
The formula =ODD(9.9) will give the output as 11, which is the nearest odd number.


9. PRODUCT
Formula:
=PRODUCT(number1, [number2], ...)

The PRODUCT function multiplies all the numbers in a data range selected in the formula and returns as the product.

Example :
If cells A1 and A2 contains numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2.

But the PRODUCT function becomes very useful when, you need to multiply many cells together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3.

Note:
If the data range is an array or reference, only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.


10. SUMPRODUCT
Formula:
=SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function, multiplies the values in the given arrays, and returns the sum of those products.

Example:
The formula =SUMPRODUCT(A2:B5, C2:D5)  will work as =(A2*C2 + B2*D2 + A3*C3 + B3*D3 + A4*C4 + B4*D4 + A5*C5 + B5*D5)

This example can also be explained by sum formula =SUM(A2:B5 * C2:D5).