Posted by Formula and functions are one of the most useful features of MS Excel programs. The formula can be used for simple calculations such as adding two numbers or for complicated calculations required for accounting, business mathematics, etc. Once you understand the basic formula format, excel can do all the calculations for you.
The formula in MS Excel begins with a (=) sing. It is a sequence of values, cell references, functions, and/or an operator contained in a cell. It creates new values from existing values.
We will learn about
Cell References
The location of a cell in a spreadsheet is referred to as its cell reference. To find a cell reference, simply look at the position of the column and row in the spreadsheet. It is a combination of column letters and row numbers, such as A1, B3, or D15. When writing a cell reference, it is remembered that the latter column usually comes first.
Using Formula
You may enter a formula in the formula bar or within a cell. The following are the separate steps to write a formula:
- Select the cell in which you want the result to be displayed.
- Type the (=) sign in the formula bar. It will automatically appear in the selected cell.
- Type reference of the cell whose result is to be calculated and operator sign (+).
- Press the enter key.
- The result will be displayed in the selected cell.
Example
Suppose, you have to calculate the value of 54+45. 54 is placed in A1 cell and 45 is typed in the A2 cell. Then, the formula for this calculation would be =A1+A2, which you can type in the formula bar and press the enter key to get the result (99).
The following arithmetic operations are used in a formula:
| Operators | Operations | Formula for (A1) and (A2) | Result (if A1=10 and A2=2 |
| ^ | Exponent | =A1^A2 | 102 = 100 |
| * | Multiplication | =A1*A2 | 10*2=20 |
| / | Division | =A1/A2 | 10/2=05 |
| – | Addition | =A1-A2 | 10-2=08 |
| + | Subtraction | =A1+A2 | 10+2=12 |
Error result
Sometimes a formula displays a result such as #VALUE! Rather than the result, it was intended to display. #VALUE! Is a type of error displayed by MS Excel. Some other errors that MS Excel returns are:
| Error | |
| #### | The column is not wide enough to display the numbers. |
| #DIV/O! | The formula contains an invalid operating- division by 0 |
| #N/A | Data are not available |
Functions
A function is a built-in formula in MS Excel that is used to carry out the common mathematical calculations. Functions save us from writing lengthy formulae. Excel can be used to perform tasks such as finding average or sum for a range of numbers.
In excel, we should write each function in a specific order called syntax.
Functions should begin with the (=) or @ sign followed by the functions name. SUM or AUG is a function name.
The third part is the arguments. It can be numbers, text, or cell reference. Arguments are enclosed within parentheses (). When there is more than one argument, each is separated by a comma.
This is an example of a function with one argument.
This is an example of a function with more than one argument.
Performing function
There are various predefined functions in MS Excel which we can use. Some of them are given below:
Sum: Add all cells in the argument.
Average: Calculation of the average of the cells in the argument.
Max: find the maximum value.
Count: find the number of cells that contain a numerical value within a range of the argument.
The various steps to use a function are:
Step 1: click on the cell where you want to apply a function.
Step 2: click on the insert function button from the function library group of the formulae tab of the ribbon.
Step 3: An insert function dialogue box appears. Choose the function and click ok. A function Argument box appears on the screen.
Step 4: Fill in the number 1 box for the first cell in the range that you want to calculate.
Step 5: Fill in the number 2 box with the last cell in the range that you want to calculate.
Step 6: The result will appear in the selected cell.
Function library
A function library is a large group of functions on the formula tab of the ribbon. These functions are available under the following categories:
Auto Sum: It calculates the sum of a range of numbers.
Recently used: All recently used functions are included.
Financial: It is a last of financial function such as accrued interest, cash flow return rates text, additional financial function, and other.
Logical: It is a list of logical functions.
Test: It includes text-based functions.
Date & Time: It includes functions to calculate date and time.
Math & Trig: it is a list of mathematical and trigonometric functions. And there are other more functions available in the group.
Autosum Feature
the AutoSum is a short-cut feature for using MS Excel’s SUM function. It provides a quick way of adding up columns and rows of the number in the spreadsheet.
The various step to apply the AutoSum feature :
Step 1: Enter the following data into cells C1 toC6 :11,12,13,14,15,16.
Step 2: Make sure that the cell C7, the location where the result will be displayed, is blank.
Step 3: Click on the home tab to select the AutoSum button from the Editing group on the ribbon to enter the SUM function into the cell. C7
Step 4: Drag the mouse to select cell C1to C6 in the spreadsheet.
Step 5: press the enter key to get the result. The answer 81 appear in the cell C7
Step 6: Click on the C7 and complete function = SUM (C1: C6) appears in the formula bar above the worksheet.
Memory bytes of MS Excel: Formula and Functions
- A formula in MS Excel starts with (=) sign.
- In the formula bar or inside a cell, you can enter the formula.
- A formula involves the use of various arithmetic operators.
- A formula may give an error result.
- The function should begin with the (=) or @ sign followed by the functions name.
- In MS Excel, different predefined functions exist.
- A function library is a large group of functions.
- The AutoSum feature is present on the home tab of the ribbon.