Excel Formula and Function

Excel Formula

A formula is an equation that performs operations on worksheet data. Excel formula can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text.

To enter a formula

• Place the cursor in the cell where the formula will appear.
• Enter an = sign.
• Enter the expression that will produce the result you want, e.g. A5+E5. This will add the value in A5 with the value in E5.
• When the formula is complete, press Enter. The result of the formula will be calculated and displayed in the cell.
• If there is an error in an Excel formula, an error message which begin with a # sign will display.

To enter a cell or range reference by pointing

• Place the cursor in the cell where the formula will appear.
• Enter the formula up to the point of the cell or range reference, e.g. to enter the formula =E2+E5, only enter the = sign.
• Using the arrow keys, move the cell pointer to the first cell reference, in this case E2. The formula will track your progress and enter the current address into the formula.
• Enter the operand, + sign.
• Using the arrow keys, move the cell pointer to the second cell reference, in this case E5. If you are calculating a range of cells, hold down the Shift key while using the arrow keys to move to the intended cells.
• Press Enter to complete the formula when you have reached the cell you require.

Excel Function

Functions are special commands used in formulas to perform mathematical processes.

To enter functions directly into the worksheet cell

• Select the cell into which the formula will be entered.
• Insert an equal (=) sign to begin the formula. The formula toolbar buttons will appear.
• Enter the name of the function [e.g. SUM], followed by an opening parenthesis [(], any arguments required for the function [e.g. E2:E5], and closing parenthesis[)].
• Press Enter. If there are no errors in the formula, the result of the function will be entered in the cell. If you activate the cell again, the function will be displayed in the formula bar.

To use the AutoSum function

• The functions can be accessed through the AutoSum icon on the Standard toolbar.
• The functions included in the AutoSum drop-down menu will insert the function and predict the arguments.
• For example, if the active cell is positioned at the bottom of the list of values, AutoSum will display a sum function with the list of the arguments.
• Sum -- add the contents of the list of arguments.
• Average -- determine the average value of the list of arguments.
• Count -- count the number of values in the list of arguments.
• Max -- return the maximum number in the list of arguments.
• Min -- return the minimum number in the list of arguments.

Example: Using the Average function

• Enter the values as shown below from B1 to B5 and select the cell B6 which the formula will be entered.

• Click the down arrow beside the AutoSum icon on the Standard toolbar and choose Average from the drop-down menu.
• If the predicted range is correct, press the Enter key. If it is incorrect, select (click and drag) the range you want with the mouse and press the Enter key.
• The result will show in cell B6. You can use the same method to do other functions such as SUM, Max, etc.

Note: Excel formula and function is one of the most powerful feature in Microsoft Excel. It is important for you to master it.