Excel - Formulas and Functions

Document Sample
Excel - Formulas and Functions Powered By Docstoc
					                                                                            iCIT Helpdesk and Training Services

Topics to be covered: MAX, MIN, AVG, IF, NESTED IF, CONDITIONAL SUM.

Formula: A sequence of values, cell references, names, functions, or operators in a cell
that produces a new value from existing values. A formula always begins with an equal
sign (=).

Function: a predefined or built-in formula. Functions can be used to perform simple or
complex calculations.

Formula Bar: A bar near the top of the Microsoft Excel window that you use to enter or
edit values or formulas in cells or charts. It displays the constant value or formula used
in the active cell. To display or hide the formula bar, click Formula Bar on the View

Formula Palette: A tool that helps you create or edit a formula and also provides
information about functions and their arguments. The palette appears below the formula
bar when you click the Edit Formula button on the formula bar or the Insert Function
button on the Standard toolbar.

           Insert Function button launches the Function Palette.

Argument: The values a function uses to perform operations or calculations. The type
of argument a function uses is specific to the function. Common arguments used within
functions include numeric values, text values, cell references, ranges of cells, names,
labels, and nested functions.

Cell References: A function can refer to a cell. The function will re-calculate any time
the value of the cell changes. A function can also refer to a range of cells. To refer to a
range of cells, enter the reference for the cell in the upper-left corner of the range, a
colon (:), and then the reference to the cell in the lower-right corner of the range. The
following are examples of references.

  The Range                  Cells included in the Range
  A15                        Cell in column A , row 15
  C1:C25                     All cells in column C, rows 1 through 25
  B2:D30                     All cell in columns B, C and D, rows 2 through 30 in each column

The Syntax of a Function: A function begins with an = (equal sign) and the name of
the function followed by an opening parenthesis. The arguments for the function are
separated by commas and end with a closing parenthesis.
Nesting: Refers to using functions as arguments for other functions. When a function is
used as an argument (or nested) it must return the same type of value that the

05/18/10           D:\Docstoc\Working\pdf\7e568958-b2b5-4927-b881-048bbde95e6b.doc                  Page 1 of 7
                                                                          iCIT Helpdesk and Training Services

argument uses or an #VALUE! error will occur. A formula can contain up to seven levels
of nested functions.

Example below: AVERAGE and SUM are nested functions of IF
Read as “IF the average of A2 through A5 is greater than 50, THEN enter the sum or B2
through B5, ELSE enter a 0”

Hands on Exercises:
Start Excel.
1. Open the file named Excel-FormulasFunctions.xls.
(The five sheets in this workbook will be used in class. Notice each sheet tab identifies
the topic covered.)

2. Make sure you are looking at the first sheet, named AvgMaxMin

05/18/10         D:\Docstoc\Working\pdf\7e568958-b2b5-4927-b881-048bbde95e6b.doc                  Page 2 of 7
                                                                          iCIT Helpdesk and Training Services

We will enter formulas to calculate the AVERAGE, MAXIMUM (highest) and MINIMUM
(lowest) book sales in cells N5, O5, P5. Note that the formulas have already been
entered in N4, O4, and P4 for you to refer to.

Reminder: Always position the cursor in the cell where you want the result of your
function or formula to go before starting your formula or function.

3. Click Cell N5.

4. Click the Insert Function button
5. Select AVERAGE from the Function Name box on the right side. (If you don’t see it,
   click All in the Function Category box to get the entire list and choose from there.)

6. Once you have found it, click OK.

The Function Argument Box will open.


7. Indicate the range of cells B5 to M5.

    (Note: Excel may try to help you by suggesting a range. If the range suggested by
    Excel is correct then this step is not necessary, however it is a good idea to double
    check. )

    If the box is taking up too much space, you may click the Collapse
    Dialog Box button which collapses the dialog allowing you to check the
    range. Click the Collapse Dialog button again to expand the window.

8. Notice the formula result is displayed near the bottom of the formula palette.

05/18/10         D:\Docstoc\Working\pdf\7e568958-b2b5-4927-b881-048bbde95e6b.doc                  Page 3 of 7
                                                                           iCIT Helpdesk and Training Services

9. Click OK.

10. The function can now be copied down to the rest of the cells in the column. Use the
    fill handle to do so.

11. Refer to the preceding steps to enter the function for MAX in cell O5 and MIN in cell
               Hint: Check the contents of cells O4 and P4 before you begin. Be sure to
               ask if you have questions.

1. Switch to the second sheet labeled SimpleIF.

In this sheet you will use a simple IF function to evaluate a number in column B (the
students’ numerical grade) and then assign an S or NC (indicating Satisfactory or No
Credit for the course) in column C. NOTE: A satisfactory grade is one which is > 65.5

Look at the contents of cell C4 and also notice the informational text off to the right.


(Tip: The quotes around S and NC (or any text) are necessary. Quotes are added
automatically when using the function argument box.)

2. Click cell C5, then click the Insert Function button and try to create the function
yourself. Please ask if you have questions.


1. Click the NestedIF sheet tab. This sheet will show you how to assign a letter grade
to a numeric score.

2. The first function has been entered for you in C2. Click C2 and check the formula
bar to see what it looks like. If you would like, try to recreate the function in Cell C3.


3. Click Cell C3.

Hint: Start with the Insert Function button and choose the IF function. To add each of
the nested IF functions for the “value_if_false” fields, click the function box (to the left of
the red X on the formula bar and choose IF. This is a tricky one. Please ask if you would
like assistance. Another method is to type the formula if you already know what it is.

05/18/10          D:\Docstoc\Working\pdf\7e568958-b2b5-4927-b881-048bbde95e6b.doc                  Page 4 of 7
                                                                          iCIT Helpdesk and Training Services


1. Click the Grades sheet tab. This sheet will allow you to apply what you have just
learned about AVG, MAX, MIN and IF. Refer to the information on the lower portion of
this sheet, the instructions on pages 2 and 3 of this handout, or to the other sheets in
the Excel file. If you have questions, be sure to ask the instructor.

05/18/10         D:\Docstoc\Working\pdf\7e568958-b2b5-4927-b881-048bbde95e6b.doc                  Page 5 of 7
                                                                          iCIT Helpdesk and Training Services


The last sheet in your workbook gives examples of a conditional sum by using the
SUMIF function. The SUMIF function adds the cells specified by a given condition or

1. Click the Cond.Sum sheet tab.

This sheet contains data about employee travel expenses.
In this exercise we want to find a total dollar amount by person. One way to do this is by
using the SUMIF function.

2. Scroll down to the bottom of the data.

3. Click cell I25 and check the formula bar to see the contents.

                          =SUMIF(B5:B22,"Dr. Alexander",I5:I22)

The first part of the formula sets the range that contains the criteria. The second tells
you what the criteria is (i.e,; is equal to “Dr. Alexander”). The third part defines the
range that contains the data that you would like to manipulate.

To check this out, change the contents of cell
G26 to Dr. Alexander.

If you would like to recreate the function, just
choose any cell and start by clicking the Insert
Function button and choosing SUMIF. Notice
the information provided at the bottom of the
sheet to help you.



Complete the
formulas in Column D
by using the Insert
Function feature or
you may simply type
in the formulas listed
in Column E.

05/18/10         D:\Docstoc\Working\pdf\7e568958-b2b5-4927-b881-048bbde95e6b.doc                  Page 6 of 7
                                                                          iCIT Helpdesk and Training Services

Now try it by adding a new feature “Analysis Tool Pac”

     Go to Tools  Add ins  Analysis Tool Pac

Let’s now use the new feature.

     Go to Tools  Data Analysis  Descriptive Statistics

     Select Input Range as B5:B14

     Select Output Range E18

     Check Summary Statistics

Microsoft Excel has hundreds of functions, many of them very complex. This class has
covered only a few of the most commonly used functions. As you explore the other
functions in Excel, refer to on-line help and the dialog box prompts for assistance. The
more you use Excel functions the easier they become.

05/18/10         D:\Docstoc\Working\pdf\7e568958-b2b5-4927-b881-048bbde95e6b.doc                  Page 7 of 7

Jun Wang Jun Wang Dr
About Some of Those documents come from internet for research purpose,if you have the copyrights of one of them,tell me by mail you!