Formulae in Excel


. 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.


. 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)


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

Most Used Excel Functions:

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)

Examples using Google Sheet:

. To get total working days, we use NETWORKDAYS function:

. To get total list of sales, we use SUM function:

. 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)