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