3.5-FORMULAS AND FUNCTIONS
Formulas and Functions enhance Excels capability calculating and manipulating
data for analytical purposes.
3.5.1 Formulas
A formula is a set of mathematical instructions that can be used to perform
calculations. Formulas are started in the formula box with an = sign. A Formula
may consist of:
- Operators : Symbols (+, -, *, /, etc.) that specify the calculation to be performed.
- References : The cell or range of cells that you want to use in your
calculation.
- Constants : Numbers or text values that do not change.
- Functions : Predefined formulas in Excel.
3.5.1.1 Create a Simple Formula
We will learn to create a formula to add two numbers:
- Click the cell where you want the formula to be defined (for example cell A3).
- Type = sign to let Excel know that a formula is being defined.
- Type the two numbers to be added with the operator. For example type 35+1330 in cell A3 (after = sign). Press Enter.
- The result of the above addition operation is displayed in the cell A3, instead of the formula that we had typed. If you select A3, the formula appears in the formula bar.
- Click the cell where you want the formula to be defined and the answer will appear (for example cell A3).
- Type = sign to let Excel know that a formula is being defined
- Type the cell number (example A1) that contains the first number to be added. Then type + operator and then the cell number (example A3) that contains the second number to be added. For example type A1+A3 in cell A3 (after = sign). Please note, if a cell does not contain a number then it is treated as containing zero.
- Press Enter.
- The result of the above addition operation is displayed in the cell A3. Cell A3 will display the value 333.
3.5.1.2 Create a Simple Formula using Point and Click Method
To create a formula using mouse:
- Click the cell where the answer will appear (B3, for example).
- Type the equal sign (=).
- Click on the first cell to be included in the formula (B1, for example).
- Type the operator sign (+ for addition or – for subtraction or * for multiplication or / for division) .
- Click on the next cell in the formula (B3, for example).
- Press Enter or click Enter button on the formula bar.
3.5.2 Using Cell References
When a cell address is used as part of a formula, it is called a cell reference
because instead of entering specific numbers into a formula, the cell address
referring to a specific cell is being used.
You have used Fill Handle in the auto fill feature in section 3.4.7. The same
feature can be used to copy formulas from one cell to another. For example, if you
have the formula =A1+B1 in cell C1, and you can use the fill handle to fill the
formula into cell C3. Note that the formula won’t appear the same in C3 as it does
in C1. Instead of =A1+B1, you will see =A3+B3 in cell C3. This is called
Relative Reference where cell references in formulas has changed cell addresses
relative to the row and column they are moved to. In relative reference, formulas
automatically adjust to new locations when they are pasted into different cells.
Sometimes, our requirement is such that we don’t want this change of cell address
on pasting. To achieve this, cells must be addressed by Absolute Reference.
In Absolute cell references, a formula always refers to the same cell or cell
range used in it. If a formula is copied to a different location, then the cell address
remains the same. An absolute reference is designated in the formula by the
addition of a dollar sign ($). It can precede the column reference or the row
reference, or both. Examples of absolute referencing are:
$A1 - here the column will not change when copied.
A$1 – here the row will not change when copied.
$A$1 – here both row and column will not change when copied.
In the above example, if we have formula as =$A$1+$B$1 in cell C1 and we copy
this formula in cell C3, then you will still see =$A$1+$B$1 in cell C3.
3.5.3 Linking Worksheets
Sometimes, you may want to use the value from a cell in another worksheet
within the same workbook in a formula. For example, the value of cell A1 in the
current worksheet and cell A3 in the second worksheet can be added using the
format "sheetname!celladdress". The formula for this example would be
"=A1+Sheet3!A3" where the value of cell A1 in the current worksheet is added to
the value of cell A3 in the worksheet named "Sheet3".
3.5.4 Functions
A function is a built in pre defined formula in Excel. One of the key benefits of
functions is that they save your time since you do not have to write the formula
yourself. For example, you could use an Excel function called Average to quickly
find the average of a range of numbers.
Excel has hundreds of different functions to assist with your calculations. Each
function has a particular syntax, which must be strictly followed for the function
to work correctly.
3.5.4.1 Function Library
The function library is a large group of functions on the Formula Tab of the
Ribbon. These functions include:
AutoSum: Easily calculates the sum of a range
Recently Used: All recently used functions
Financial: Accrued interest, cash flow return rates and additional financial
functions
Logical: And, If, True, False, etc.
Text: Text based functions
Date & Time: Functions calculated on date and time
Math & Trig: Mathematical Functions
You can visit each of these functions in the library to know more about them.
3.5.4.2 Insert a Function
To insert a function:
- Click the cell where you want the function applied
- Click the Insert Function button on the formula bar. The Insert Function dialog box opens.
- Choose the function from the dialog box. You may search on a particular function in the dialog box or change the category and select the function. Help for each function is available right there in the dialog box.
- Click OK. Function Arguments dialog box opens.
- Select the cells or range of cells for function arguments and click OK.
- The Function is added to the formula bar
