Formulae, Cell referencing (absolute & relative) and worksheet ranges

Cell Range and Referencing

A range is two or more cells which can be adjacent (all cells are along side) or non-adjacent. Adjacent ranges are given as

Start cell address: End cell address Eg. A1:A3 or A1:B3

Adjacent range can be selected by mouse by dragging from start to last and pressing control key for large ranges to scroll across the sheet. R1C1 reference style is used for row (R1) and column(C1).

Range Names – Adjacent ranges can be given names for easy access. Name is given in cell address box in the formula bar. Cell address in formula can be of two types relative and absolute reference.

• Relative Reference – It is in relation to the position of the cell that contains the formula and automatic adjustment is done like if cell A3 has the formula =A1+A2 and if cell A3 is copied to cell B3, the formula in cell B3 becomes =B1+B2.
• Absolute Reference – Reference to cell or range does not change when the formula is copied. A $ (dollar sign) character before the column or row is added like if cell A3 has the formula =$A$1+A2 and if cell A3 is copied to cell B3, the formula in cell B3 becomes =$A$1+B2.

Formulae is the most used facility of MS-EXCEL for calculating values to return a result. Each Excel formula must have three key elements: the equal sign (=) that signifies start of a formula, the values or cell references to be calculated, and the mathematical operators, such as a plus sign (+) for addition or a minus sign (-) for subtraction. Operators act on data in formula to give result as

Operator	What It Does	Example Formula	Result
+	Addition	=2+2	4
–	Subtraction	=2-2	0
*	Multiplication	=2*2	4
/	Division	=2/2	1
=	Equal to	=A2=A3	True/False
<	Less than	=2<3 or =A3<A5	True
>	Greater than	=2>3	False

A formula is defined as, a sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. In case of multiple operators in a formula, different operators have different importance or precedence and are executed before others as

• Operations enclosed in parentheses
• Multiplication and division operations
• Addition and subtraction operations

Functions
They are in-built formulas which do specific task and given as
Function name (input1, input2…)
Function name is a specific function name and input are as per data needed by formulae. Different types of functions are mathematical, statistical, date and time, logical and string functions. Different types of functions are mathematical, statistical, date and time, logical and string functions like
ROUND Function – Rounds a number to a specified number of digits.
ROUND (number, num_digits)
Number is number to round and Num_digits gives number of digits to round upto.
COUNT Function – Counts number of cells that contain numbers
COUNT (value1, value2…)
Value1, value2, … are cell address.
MAX Function – Returns the largest value in a set of values. Its usage is
MAX (number1, number2…)
MIN Function – Returns the smallest number in a set of values. Its usage is
MIN (number1, number2…)
AVERAGE Function – Returns average of numbers given
=Average (number1, number2, address1:address2)
SUM function – To calculate the sum of the values in the cells E3 to E6 the formula would be =SUM (E3:E6) or =E3+E4+E5+E6, the difference between the two is that in earlier on we have taken a range (a collection of consecutive cells) and calculated the sum by using the sum function