[vip_students] Excel Tutorial:Lesson 25,Understanding and Using Functions
- From: "NCBI Technical Support" <paul.traynor@xxxxxxx>
- To: <vip_students@xxxxxxxxxxxxx>
- Date: Thu, 10 May 2007 21:35:03 +0100
Topic: Understanding and Using Functions
Introduction. Using formulas to manipulate and analyze data is an important
part of Excel. While many of the formulas you write will be simple, you may
need to create formulas that perform complex calculations. To avoid lengthy
and confusing formulas, use Excel's functions. Excel functions represent the
most commonly used formulas. Examples of simple functions are SUM, which
calculates the sum of the referenced cells, and AVERAGE, which calculates
the average of the referenced cells. A function can be used alone or as an
element in a larger formula.
Functions simplify formulas and can save you keystrokes. For example,
instead of typing the formula =A1+A2+A3+A4+A5+A6 into a cell, you can use
the SUM function to simplify the formula: =SUM(A1:A6).
Function Syntax. When using functions, use the following syntax: equal
sign, function name, and arguments. An argument is the value to be used in
the calculation. Include arguments in parenthesis and separate each argument
with commas. The following is a formula that uses a function with proper
syntax: =SUM(A12, B6:B10, E14).
Exercise Objectives. In the following two exercises, you will use the SUM
function to calculate the total for a range of cells.
Exercise: Using Simple Functions
Complete the following eight steps to review the expenses and enter
functions that will total the yearly expenses.
Step 1: Move to cell A9 by pressing Ctrl-G to open the Go To dialog box.
Type A9 in the Reference field, and then press Enter.
Excel selects cell A9. JFW announces, "Expenses A9."
Step 2: Press the Right Arrow key until you have read the data in cells B9
through F9. Then, press Home to select cell A9 again.
Step 3: Select cell A10 by pressing the Down Arrow key until JFW announces,
"Wages A10."
You will add the wage figures for each quarter to establish the total wage
expenses for the year. The quarterly figures appear in cells B10 through
E10. The total will appear in cell F10.
Step 4: Move to cell F10 by pressing the Right Arrow key until JFW
announces, "Blank F10." Note the quarterly wage figures as you move to cell
F10.
Tip: You can verify you are entering the formula in the total Year column
by pressing Alt-4. (You can use this keystroke to read the column label for
this data because the label is in the fourth cell of this column.)
Step 5: Type the following formula into cell F10: =SUM(B10:E10). Then,
press Insert-Up Arrow to verify.
Take a moment to decipher this formula. The equal sign directs Excel to
treat the data in the cell as a formula. The word "SUM" is a function; it
directs Excel to add the cells in the argument. The argument (B10:E10)
indicates that Excel will use the data in cells B10 through E10.
Using the SUM function is better than typing all of the cell references that
you want to add, especially when you are working with numerous cells and
worksheets.
Step 6: Press Enter to accept the formula. JFW announces, "Blank F11."
Excel performs the calculation, displays the results in F10, and moves the
selection cursor to cell F11.
Step 7: Press the Up Arrow key to select cell F10 and read the formula
result. JFW announces, "5950 Has Formula F10."
Remember, when you enter a formula, only the result of the formula appears
in the cell and only the formula appears in the edit field on the Formula
Bar.
Step 8: Press Ctrl-F2 to read the information in the edit field on the
Formula Bar. JFW announces, "=SUM(B10:E10)."
Now, you will add the supply figures for each quarter to determine the total
supply expenses for the year.
Exercise: Entering Another Formula with the SUM Function
Complete the following five steps to insert a formula for calculating the
yearly supply expenses. The quarterly figures appear in B11 through E11. The
total will appear in cell F11.
Step 1: Move the focus to cell A11 by pressing Ctrl-G to open the Go To
dialog box. Type A11 in the Reference field, and then press Enter.
Excel moves to cell A11. JFW announces, "Supplies A11."
Step 2: Select cell F11 by pressing the Right Arrow key until JFW
announces, "Blank F11." Note the quarterly supply figures as you move to
cell F11.
Tip: You can verify you are entering the formula in the total Year column
by pressing Alt-4.
Step 3: Type the following formula into cell F11: =SUM(B11:E11). Then,
press Enter to accept the formula. JFW announces, "6900 Has Formula F12."
When you press Enter, Excel performs the calculation, displays the results
in F11, and moves the selection cursor to cell F12.
Step 4: Press the Up Arrow key to select cell F11 and read the formula
results. JFW announces, "950 Has Formula F11."
Step 5: Press Ctrl-F2 to read the information in the edit field on the
Formula Bar. JFW announces, "=SUM(B11:E11)."
You will learn more about using functions later in this lesson.
What You Learned. In this topic, you learned:
Excel functions represent the most commonly used formulas. The SUM and
AVERAGE functions are examples of simple functions.
Use Excel's functions to avoid lengthy and confusing formulas. For example,
using the SUM function is better than typing all of the cells that you want
to add, especially when you are working with numerous cells and worksheets.
A function can be used alone or as an element in a larger formula.
When using functions, use the following syntax: equal sign, function name,
and arguments. An argument is the value to be used in the calculation
performed by the function. Include arguments in parentheses and separate
each argument with commas. The following is a formula that uses a function
with proper syntax: =SUM(A12, B6:B10, E14).
********************************************************************
NOTICE: The information contained in this email and any attachments
is confidential and may be privileged. If you are not the intended
recipient you should not use, disclose, distribute or copy any of
the content of it or of any attachment; you are requested to notify
the sender immediately of your receipt of the email and then to
delete it and any attachments from your system.
NCBI endeavours to ensure that emails and any attachments generated
by its staff are free from viruses or other contaminants. However,
it cannot accept any responsibility for any such which are
transmitted. We therefore recommend you scan all attachments.
Please note that the statements and views expressed in this email
and any attachments are those of the author and do not necessarily
represent the views of NCBI
********************************************************************
Other related posts:
- » [vip_students] Excel Tutorial:Lesson 25,Understanding and Using Functions
