# Formulae in Excel

### Formula:

. formula is an expression which calculates the value of a cell.
. It appears in formula bar, when we press enter, it gives result.
It follows operator precedence.

### Function:

. Predefined formula is called a function.
. Excel provides lot of functions for easier data calculations.

#### 1.SUM Function:

. It returns the sum of cells in given range.
. Syntax: =SUM(cell range)
. Example: =SUM(D3:D7)

#### 2.IF Function:

. It is used for test specific condition.
. Syntax: =If(condition,value_if_true,value_if_false)
. Example: =If(C5>35,"Pass","Fail")

#### 3.COUNT Function:

. It is used to count number of cells within the given range.
. Syntax: =COUNT(cell range)
. Example: =COUNT(C5:C8)

#### 4.DAYS Function:

. It is used to get number of days between two dates.
. Syntax: =DAYS(end_date,start_date)
. Example: =DAYS(C4,B4)

#### 5.CONCATENATE Function:

. It is used to combine contents of cells.
. Syntax: =CONCATENATE(Cell,"Symbol",Cell)
. Example: =CONCATENATE(B5," got ",C5,"marks")

#### Most Used Excel Functions:

FUNCTIONPURPOSESYNTAX
AVERAGETo get average for given cell range=AVERAGE(cell range)
MINTo get minimum value from cell range=MIN(cell range)
MAXTo get maximum value from cell range=MAX(cell range)
TRIMTo remove extra space from text=TRIM(text)
CLEARTo remove line breaks and non-printing characters from text=CLEAR(text)
ABSTo get absolute value of the number/cell=ABS(number/cell)
SQRTTo get square root of the number/cell=SQRT(number/cell)
COUNTATo count number of non-blank cells =COUNTA(cell range)
COUNTIFTo count number of cell that meet condition=COUNTIF(cell range,"condition")
LENTo count number of characters in single cell=LEN(cell)
NOWTo get current date and time=NOW()
NETWORKDAYSTo get number of working days between two days=NETWORKDAYS(start_date,end_date)
ROUNDTo round off numbers to given number of digits=ROUND(cell/number, num_digits)

. To get total working days, we use NETWORKDAYS function:
=NETWORKINGDAYS(B3,C3)

. To get total list of sales, we use SUM function:
=SUM(E3:G3)

. To display a column indicating Sales of employees, we use CONCATENATE function:
=CONCATENATE("Sales of ",A3," is ", H3)

. To indicate whether employee gets hike or not based on  total sales, we use IF function:
=IF(H3>200,"Hike", "No Hike")

. This table has the following functions:
> Current date and time: =NOW()
> Absoulte value: =ABS(B6)
> Round up: =ROUNDUP(C6,0)
> Square root: =SQRT(D6)
> Length of numbers: =LEN(E6)