Understanding the basic Functions of MS Excel

Explain following Excel functions example

(a) Sum

(b) Product

(c) Average

(d) Max  

(e) Min

(f) Round

(g) Count

(h) If

(A) SUM

The Sum function adds all numbers in a range of cells and returns the result. The syntax for the Sum function is :

Sum (number 1, number2, ... number n) or sum (cell 1, cell2... cell n) or Sum (cell1 : ce112).

For Example

Sum (10, 2, 13) returns 25

Sum (A1, A2, A3) will display sum of cell Al, A2 and AB.

Sum (A1 : A2) returns sum of all values present in cells from A1 to A2

When using numbers ng argument, Sum Function accepts up to 255 numbers.

(B) PRODUCT

The Product function multiplies all numbers in a range of cells and returns the product. The syntax for the Product function is:

Product (number 1, number2, ... number n) or Product (cell 1,

cell2.... cell n) or Product (cell1 : cell2)

For example

Product (10,2) returns 20

Product (Al, A2, A3) will display product of cell Al, A2 and A3.

Product (A1 : A 10) returns product of all values present in cells from Al to A 10.

When using numbers as argument, Product Function accepts up to 255 numbers.

(C) AVERAGE

The Average function returns average from all numbers in a range of cells or from given values. The syntax for the Average function is:

Average ( number 1, number2, ... number n ) or Average (cell1, ce112. ... cell n) or Average ( cell 1 : cell2 )

For example

Average (10, 11, 12) returns 11

Average (A1 : A 10) returns Average of all values present in cells from A1 to A 10.

Average (A1, A2, A3) returns average of values of cells Al, (g) (A2 and A3.

When using numbers, Average Function accepts up to 255 numbers.

 (D) MAX

The Max function returns largest value from the cell range. The syntax for the Max function is :

Max( number 1, number2, number n ) or Max (cell 1, cell2 cell n) or Max (cell1 : cell2)

For example

Max (A1, A2, A3) returns maximum value from cells A1 A2 and AB.

Max(A1 : A 10) returns largest value present in cells from

When using numbers, Max Function accepts up to 255 numbers.

(E) MIN

The Min function returns smallest value from cell range. The syntax for the Min function is :

Min (number1, number2, ... number n ) or Min (cell1, cell2. ... cell n) or Min (cell1 : cell2)

For example

Min(10,12, 13) returns 10

Min (Al, A2, A3) returns minimum value from cells A1, A2 and A3.

Min(A1 : A 10) returns smallest value present in cells from A1 to AIO.

When using numbers, Min Function accepts up to 255 numbers.

(D ROUND

Round function returns a number rounded to a specified number of digits. The syntax for the Round function is:

Round (number, digits) or Round (cell1 : cell2, digit)

number is the number to be round up.

digit is the number of digits up to which number should be round.

For example

Round(123.456,2) return 123.46

Round(123.456, 1) return 123.5

Round (123.456, 0) Return 123

(G) COUNT

The Count function counts the number of cells that contain some data in given range. Count function is used to get the number of entries in a range or array of numbers. The syntax of Count function is as follows:

Count (number1, number2, „. number n) or Count(cell1, cell2,... cell n) or Count(cell1:cell2)

For example,

Count (A1:A10) will display result 7 if only seven of the cells in the range contain data and other three are empty.

(H) IF

The “IF” function returns one value if a specified condition evaluates to TRUE, and another value if that condition evaluates to FALSE. The syntax of If function is as follows:

If(logicaltest, valueiftrue, valueiffalse)

Where logical text is any expression that can be evaluated to true or false.

Valueiftrue - is the value to be returned if logical test is true.

Valueiffalse - is the value to be returned if logical test is false.

For Example

If(a1>100,”more”,”Less”) returns “more” if A1 is grater than 100, and “less” than or equal to 100.