Introduction to Formulas and Functions in Excel - Part Two

This is the second introductory part of Excel, to familiarize with Formulas and Functions in Excel. If you have missed part one, read it here. To begin with, formulas is the main backbone of a spreadsheet, without it, it would have been just a normal word-processing document. This part will introduce you to Formulas, to help you speed up with spreadsheets.
An Excel formula is a composite text that helps one to calculate something and throw a result.
In general, a formula may consist of any of these elements:

•  Mathematical operators, eg + (for addition) and * (for multiplication)
• Cell references (including named cells and ranges)
• Values or text
• Functions (such as SUM or AVERAGE)

To understand, what a function is, please go back to part one of my post.
Below is a list of commonly used operators in Excel.
+ Addition
– Subtraction
* Multiplication
/ Division
^ Exponentiation
& Concatenation
= Logical comparison (equal to)
> Logical comparison (greater than)
< Logical comparison (less than)
>= Logical comparison (greater than or equal to)
<= Logical comparison (less than or equal to)
<> Logical comparison (not equal to)

Since there are operators in Excel, it shall also follow the rules of Mathematics, and shall follow the rule of operator precedence. Below is a table showing the same.

Symbol	Operator	Precedence
^	Exponentiation	1
*	Multiplication	2
/	Division	2
+	Addition	3
–	Subtraction	3
&	Concatenation	4
=	Equal to	5
<	Less than	5
>	Greater than	5

One can use parentheses to override the Excel’s order of precedence. Formulas or expressions given in parentheses are always evaluated first. For example, if we have an expression as =A2-C1*D4 , Excel will first evaluate multiplication of C1 and D4 and then get it subtracted from A2. However if there is a parenthesis like this =(A2-C1)*D4 , Excel will first subtract the value of C1 from A1 and then multiply it with D4.  Although it’s a good idea to use parentheses, wherever necessary and to even make it look better, they aren’t strictly necessary. 

Use of Functions:

An Excel function is used to simplify a formula significantly. For example to calculate the sum of the values in 10 cells (D1:D10) using formula , you would have to use it like this:

=(D1+D2+D3+D4+D5+D6+D7+D8+D9+D10)

Doesn't it look confusing, and error prone? Suppose you need to edit this formula if you added another cell to the range, you would have to browse thorough the formula again and rework on it. Thats why Excel has Functions. An Excel Function =SUM(D1:D10) will perform the same evaluation.

How to enter formula or function in Excel:

In Excel, you start entering a formula or function by using equal to (=) and typing the formula or function name. eg = SUM  or =A1+D7. If you are typing a function, Excel will automatically suggest the function and arguments automatically through a scrolling drop down menu, from where you can actually select using mouse.  You can actually type formulas or functions manually, or even use mouse to select cells.  If you wish to edit a formula or expression, just select the cell with mouse and press F2.

Try out cell referencing in Excel using a mouse, for a formula =C1*D4-A1 by 

1. Typing =

2. Select C1 by mouse

3. Type *

4. Select D4 by mouse or by directional arrows on keyboard

5. Type -

6. Select A1

7. Press enter

Tip: Suppose you wish to use a particular cell fixed (eg C3), in a series of calculations on multiple rows. Type the formula in the topmost row e.g. =B3*C3-D3, edit it using F2 and put $ symbol before C an 3 like this =B3*$C$3-D3. Now when you double click on the cell, or drag on the right cell corner, the same formula will be copied with changing ranges but fixed D4 cell.

Should you have any queries, please comment.