Excel has provided a vast formula to perform different operation.

**1. Sum
**

Used for adding numbers

**Syntax
**

Sum(Number 1, Number 2.,Number 9) or SUM( Starting location of data: Last location of data)

**Example
**

sum(20,30,40) = 20+30+40 = 90

SUM(A1:A8) = add all numbers updated in column A from row 1 to 8

SUM(A1:B8) = add all numbers updated in column A and column B from row 1 to row 8.

**2. Count**

Used to count numbers in the selected range.

**Syntax
**

COUNT( Value 1, Value 2, …. , Value N) or COUNT(First cell of data range: Last cell of data range)

**Example
**

COUNT(B1:B16) is equal to count of numerical data points in column B from row 1 to row 6.

**3. Average
**

Used to average/mean of the given set. i,e the sum of all data points divided by count of data points.

**Syntax
**

AVERAGE(Value 1, Value 2…., Value N) or COUNT (First cell of data range: Last cell of data range)

**Example
**

AVERAGE( B1:B16) = SUM( B1:B16)/COUNT(B1:B16)

This function will not consider empty cells and non-numeric cells.

**4. IF Function
**

IF function is used to perform required action if a predefined condition is either TRUE or FALSE.

IFERROR is used to manage error evaluated while performing another function.

**Syntax
**

If ( Logical condition, Value_if_true, Value_if_false)

IfERROR(value, value_if_error)

**5. VLOOKUP**

used to find a required value in a table in a corresponding referred now

**Syntax
**

VLOOKUP(Lookup_value, table_array,col_index_num),[range_lookup]

**6. Offset
**

Returns a reference to a range that is specified number of rows and columns from a cell or range of cells.

**Syntax
**

OFFSET(reference, rows, cols,[height],[width])

**7. COUNT employees
**

From the data, calculate the count of data equal to, not equal to, less than or higher than a given number. COUNTIF can be used.

**Example
**

Employees selling more than 40

= COUNTIF(c2:c33,”> 40)

**8. MAX , MIN
**

MAX will return largest numeric value of the range , MIN will return the smallest numeric value if the range.They will include only numeric values

MAXIF MINIF will return largest and smallest values respectively only among the cells.

**Syntax
**

MAX (Range); MIN(Range);

MAXIF([Max_Range], Criteria Range 1, Criteria Range 2, Criteria Range N)

MINIF [(MIN_Range], Criteria Range 1, Criteria Range 2, Criteria Range N)

**9. Round
**

ROUND function is used to round number to a specified number. ROUNDUP, ROUNDDOWN can be used to round number away from Zero.

**Syntax
**

ROUND(Number,digit)

ROUNDUP(Number,digit)

ROUNDDOWN(Number, digit)

**10. Replace
**

Used to search and replace for character to text

**Syntax
**

REPLACE(old_txt, Start num,num_char_to_replace, new text)