Working with Functions
The status bar provides constant feedback on any workbook that is currently displayed. One example of such feedback is the AutoCalculate feature. By default, AutoCalculate displays the sum of values in the selected range. By looking at the status bar, we can see that AutoCalculate displays Sum=883. This is the sum to the range C10:E11.
The AutoCalculate area in the status bar displays the Sum of a selected value. But you can change the AutoCalculate formula by Right-clicking anywhere on the status bar then AutoCalculate pop-up menu will display. A function will be checked, indicating that it is the formula being used by the AutoCalculate feature. When you change the AutoCalculate formula, the status bar will displays the figures in the formula (e.g. Average=188.6). the built-in formulas in the AutoCalculate feature, called functions, represent only a few of the over 200 functions built into Excel.
Functions start with an equal sign (=) and generally have two components: the function name and its arguments. The arguments component must be enclosed in parentheses. In most functions that arguments are a cell range. Let’s enter a few of these functions into the worksheet now. Select cell C13, type =sum( as you type, the formula is displayed in the cell and in the formula bar. Use the mouse to select the range like C7:C11. As you selected the range, Excel updated the formula in the formula bar with the range selection. To complete the function, type ) and press Enter. E.g.2 select cell C15 type=average( use the mouse to select the range like C7:C11
we can omit the closing parenthesis when entering a function that needs only one pair of parentheses. Excel will add the closing parenthesis automatically. To complete the formula, rather than press the Enter key, click on the Enter button in the formula bar. When you click on the Enter button the cell receiving the formula remains the active cell.
In cell a HIGH cell, enter the Max function to find the highest number within the range C7:C11. Once again, use the Enter button to complete the formula entry.
One of the most commonly used functions is the Sum function. Select a cell, click on the AutoSum button. When you clicked on the AutoSum button, Excel applied the Sum function and a suggested range to be added in the formula bar. If the suggested range is not correct, you can select the correct range before applying the function. Click on the Enter button to apply the function. You can use the AutoSum button to apply the Sum function to each cell in the range in one step. E.g. select the range C8:F11. Click on the AutoSum button.
Another way to apply functions to a worksheet is to use the Edit Formula button. By using this button, you can select a function from a list. E.g. select cell F13. Click on the Edit Formula button. You can display the function list by clicking on the function list drop-down arrow. Click on the function list drop-down arrow. Select SUM from the function list. After selecting a function, Excel suggests a range and provides additional information about what the function can do. Excel has suggested that we want to add cells c13:E13, and that is correct. Click on OK. By using the Sum function, the correct formula has been entered into cell F13.
Another way to add formulas to our Excel worksheets is to use the Paste Function dialog box. The Paste Function dialog box lists each function by category. E.g. select cell F15. Click on the Paste Function button. The paste Function dialog box will display. From the Function category list, select All. From the Function name list, select AVERAGE. Click on Ok. After you select a function, Excel suggests a range and provides additional information about what the function can do, just as it did when we used the Edit button. This time, however, the range suggestion is incorrect. To hide the dialog box, click on the Collapse button. (the dialog box has been collapsed, enabling you to select a different range). Select range F7:F11. To display the dialog box, click on the Expand button. (NB: the range you selected is displayed in the Number 1 box). Click on Ok.
Editing Cell Contents
You can enter text that is longer than a cell’s width into one cell. The additional text is displayed in the cells immediately to the right, if these cells are empty. E.g. select cell B2. Type Bem T Celestine and press the Enter key. Some of the text looks as if it has spilled into cell C2. E.g. select cell C2. Observe that cell C2 is empty. The text that appears in cell C2 is contained in cell B2. The display space is just being “borrowed” because C2 is empty. We can change this. Type and press the Enter key. Now that cell C2 contains the number, cell B2 cannot borrow the display area. Let’s verify that the text in cell B2 is unchanged. Select cell B2. The formula bar displays the text “Bem T Celestine”. The text was not deleted. To enable the long text to be displayed, we need to clear the contains in cell C2. E.g. select cell C2. Press the Delete key.
There are several methods you can use to edit the contents of a cell. You can double-click on a cell or press the F2 key, which is the Edit key in Excel. Once Excel is in Edit mode, you can use the mouse or the keyboard to perform the edit. E.g. Double-click on cell B2. The mode indicator on the status bar now displays “Edit” and the formula bar contains the Cancel, Enter, and Edit Formula buttons.
Editing cell contents in a cell that contains a formula.
You might need to edit a formula if an argument is incorrect, or if the formula contains a circular reference. A circular reference occurs when a formula refers to the cell in which it resides.
When a worksheet contains a circular reference, an error message box is displayed when you open the worksheet. The Circular Reference toolbar is displayed and the message in the status bar identifies the cell that contains the circular reference even if it may not be as easy to identify the error. The Circular Reference toolbar contains tools to help you find and correct a circular reference. NB: a blue dot appears in a cell, it identifies a cell as one that contains a circular reference. If there were more than one cell containing a circular reference, they would appear in the Circular Reference toolbar’s drop-down list. Click on the Trace Precedents button to graphically displays the cell that are contained within the formula of a cell.
Regards,
BEM T. CELESTINE
Social Plugin