Introduction Formulas Ms Excel 2007
Formulas Ms Excel 2007:
Formulas Ms Excel 2007:A formula performs calculation on data and displays the result I a cell. And displays the result in a cell. A formula begins with equal sign =. It can be used for different types of calculation. For example, it can be use to calculation the total marks of students. A formula is entered in a cell. It appears I the formula bar when that cell is activated.
Entering a Formula in Excel
The following procedure is use to enter formula in Excel.
- Click any cell to enter the formula.
- Type the equal sign =
- Types a Formula and press Enter key. The result will appear in the cell and the formula will appear in the formula Bar.
Example 1
- Move cursor to cell A1.
- Type 1 and press Enter.
- Type =A1+A2 in cell A3 and press Enter.
- Note that cell A1 has been added to cell A2 and the result is shown in cell A3.
Example 2
- Type 5 in cell B1 and press Enter.
- Write 3 in cell B2 and press Enter.
- Type = B1 –B2 in cell B3 and press Enter.
- Cell B1 is subtracted from B2 and result appears in cell B3.
- Click cell B3 and look at Formula bar. You can divide and multiply in the same way.
Arithmetic Operators in Excel:
Arithmetic operators are use to perform on the elements of a formula. Excel use the standard arithmetic operators as follows:
| + | Addition | =A7+A9 |
| - | Subtraction | = A7-A9 |
| * | Multiplication | =A7*A9 |
| / | Division | =A7/A9 |
| % | Percent | =50% |
| ^ | Exponents | =5^3(5 raised to power 3) |
Precedence of operations:
The order in which operators are evaluate in a mathematical expression is called operator precedence. The operator with the highest precedence is evaluate first.
| Parentheses | ( ) |
| Negation | - |
| Percentage | % |
| Exponentiation | ^ |
| Multiplication and division | *and / |
| Addition and substation | + and - |
| Concatenation | And |
Cell Reference:
Each cell in has a reference number. A cell reference in a formula refers to the contents of reference cell. The result of formula is automatically will be change if contents reference cell are change. A cell reference consists of column name and row number. For example, B20 refers to row 20 in column B. The formula = G8 +Y10 will add values of G8 and Y10.
Range all cells can be reference as range if formula use continuous cells. The range is specify as follows:
- Address of the first cell: address of the last cell.
For example, the cells D3, E3 ,F3, G3 can be reference as D3:G3.
Relative, Absolute and mixed Referencing:
Referencing cells by column and row labels such as A1 is called relative referencing if a formula containing relative referencing is copied from one cell to another, spreadsheet changes cell addressed relative to the new cell address.
Example
if a simple addition formula in cell C1 i.e. =(A1+B1) is copy to cell C2, the formula will change to = ( A2+B2) to reflect the new row.
Absolute Referencing:
Referencing cells by column and row labels along with “$”such as “$A1” is called absolute referencing. if a formula containing absolute referencing is copied from one cell to another, Excel does not change cell addresses.
Example
and In previous example, the formula in cell C1 would read =($A$1+$B$1) if the value of cell C2 should be the sum of cells A1 and B1.Both the column and row of both cells are absolute and will not be change when copies.
Mixed Referencing.
Mixed addressed are the cell referencing that combine absolute and relative addressing, making either the row or the column absolute.