Using Functions

Instructions: You will enter functions using the keyboard, toolbar and Paste Function com and.

Comment: Using functions in formula cuts down a lot of the typing involved in setting up spreadsheets as well as adding much more calculating power.

Activity 5.1 We are going to replace the formula in column F with a function.

Select the cell F5. Type =SUM(C5:E5). Press <Enter>.

The displayed result should not change, but it is now being calculated using a function rather than the long-winded =C5+D5+E5.

Activity 5.2 Select cell F6. We are now going to enter the same formula using a toolbar shortcut. On the toolbar, click the Autosum button . The text =SUM(C6:E6) appears in the cell with the text C6:E6 highlighted and an active dotted box around the range C6:E6. Excel has created the SUM function for you and selected the most likely range of cells to add up. Press <Enter> to accept the formula.

Activity 5.3 Select cell C20. We are going to enter the averages of each column into row 20. We could just type the function name, but there is a facility to help us remember what it is called and what arguments (the bits you put in the brackets) it expects.

From the Insert menu select Function... option, The following dialogue box will appear.

Activity 5.4 Select Statistical from the Function Category, and then AVERAGE from the Function Name. Click <Finish>. The AVERAGE function is pasted into cell C20. The text number1 is highlighted. Using the mouse drag over the range C5:C8. The cell references are automatically added into the formula. Now select the text number2 in the Formula bar and drag the mouse over the range C10:C13. Finally replace the last three dots with the range C15:C18. Press <Enter>. Your average formula is complete and should now read:

=AVERAGE(C5:C8,C10:C13,C15:C18).

OSU | CoF | Community | Search | Forestry Computing Helpdesk