Formula:
. A 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)
. 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:
| FUNCTION | PURPOSE | SYNTAX |
|---|---|---|
| AVERAGE | To get average for given cell range | =AVERAGE(cell range) |
| MIN | To get minimum value from cell range | =MIN(cell range) |
| MAX | To get maximum value from cell range | =MAX(cell range) |
| TRIM | To remove extra space from text | =TRIM(text) |
| CLEAR | To remove line breaks and non-printing characters from text | =CLEAR(text) |
| ABS | To get absolute value of the number/cell | =ABS(number/cell) |
| SQRT | To get square root of the number/cell | =SQRT(number/cell) |
| COUNTA | To count number of non-blank cells | =COUNTA(cell range) |
| COUNTIF | To count number of cell that meet condition | =COUNTIF(cell range,"condition") |
| LEN | To count number of characters in single cell | =LEN(cell) |
| NOW | To get current date and time | =NOW() |
| NETWORKDAYS | To get number of working days between two days | =NETWORKDAYS(start_date,end_date) |
| ROUND | To 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:
=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)
