Objectives - Store & Retrieve Data Anywhere | Amazon Simple ...

Objectives - Store & Retrieve Data Anywhere | Amazon Simple ...

Exploring Microsoft Office Excel 2010 by Robert Grauer, Keith Mulbery, and Mary Anne Poatsy Chapter 2 Formulas and Functions Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 1 Objectives

Use semi-selection to create a formula Use relative, absolute, and mixed cell references in formulas Avoid circular references Insert a function Total values with the SUM function Insert basic statistical functions Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 2 Objectives (continued) Use date functions Determine results with the IF function

Use lookup functions Calculate payments with the PMT function Create and maintain range names Use range names in formulas Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 3 Using Semi-Selection to Create a Formula Semi-selection uses the mouse pointer to build a formula containing cell references or ranges This technique is also called pointing

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 4 Cell References Excel offers three types of cell references for use when a formula is copied Absolute $A$1 Relative A1 Mixed $A1 or A$1 $ indicates that the row number or column letter will not be modified

during a copy Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. . 5 Relative Cell References When the formula shown in the formula bar is copied, relative address A8 is modified Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 6

Absolute Cell References When the formula shown in the formula bar is copied, absolute address $B$5 is fixed Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 7 Mixed Cell References In mixed reference $A1, the column is fixed, but the row may be altered during a copy In mixed reference A$1, the row is fixed, but the column may be altered during a copy

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 8 Avoiding Circular References A circular reference error occurs if a formula refers to itself Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 9 Function Basics

An Excel function is a predefined formula that performs a calculation Category Compatibility Cube Database Date & Time Description Contains functions compatible with Excel 2007 and earlier. Returns values based on data in a cube, such as validating membership or returning a members ranking. Analyzes records stored in a database format in Excel and returns key values, such as the number of records or averages value in a field. Provides methods for manipulating date and time values.

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 10 Function Basics (continued) Category Description Engineering Calculates values commonly used by engineers, such as conversions. Financial Performs financial calculations, such as payments, rates and

present/future values. Information Provides information about the contents of a cell, typically displaying TRUE if the cell contains a particular data type, such as a value. Logical Performs logical tests and returns the value of the tests. Includes logical operators such as AND, OR, and NOT. Lookup & Reference Looks up values, creates links to cells, or provides references to cells in a worksheet.

Math & Trig Performs standard math and trigonometry calculations. Statistical Performs statistical calculations, such as averages or standard deviation. Text Manipulates text strings, by combining words or converting cases. Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 11

Function Terminology Syntax is the set of rules that govern correct formation of a function An argument is an input, such as a cell or range A function begins with the equal sign (=) followed by the function name and arguments in parentheses Example: =SUM(A1:A3) Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 12 Inserting a Function

When a function is typed, Formula AutoComplete displays a list of functions matching the partial entry Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 13 Inserting a Function A function ScreenTip is a small pop-up description that displays the function arguments Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

14 Insert Function Dialog Box Use the Insert Function dialog box to search for a function or select one from a list Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 15 Function Arguments Dialog Box The Function Arguments dialog box offers help on each argument

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 16 Totaling Values with SUM The SUM function returns the mathematical sum of some number of cells or ranges; for example: =SUM(A1:A3) =SUM(A1,B3,C5) =SUM(A1:B3,C5:E8) Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

17 Basic Statistical Functions Common statistical functions include: AVERAGEarithmetic mean MEDIAN midpoint value MIN minimum value MAX maximum value COUNT number of values in range COUNTA number of nonempty cells COUNTBLANKnumber of empty cells Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 18

Basic Statistical Functions Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 19 Other Math & Trig Functions Function Syntax Description =ABS(number) =FREQUENCY(data_array, bins_array) =INT(number)

=MODE.SNGL(num1, [num2],) =PI() =PRODUCT(num1, [num2],) =RANDBETWEEN(bottom, top) Displays the positive value of a number. Counts how often values appear in a given range. Rounds a value down to the nearest whole number. Displays the most frequently occurring value in a list. Returns the value of pi accurate to 15 digits. Multiplies all values within the argument list. Generates a random number between two values.

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 20 Other Math & Trig Functions Function Syntax Description =RANK.AVG(number, ref,[order]) =RANK.EQ(number, ref,[order]) =ROUND(number, num_digits) =SUMPRODUCT(array1, [array2],)

=TRIMMEAN(array, percent) =TRUNC(number, num_digits) Identifies a values rank within a list; returns average rank for identical values. Identifies a values rank within a list; the top rank is identified for identical values. Rounds a value to a specific number of digits. Finds the result of multiplying values in one range by related values in another column and adding products. Returns the average of the internal values in a range by excluding a specified percentage at the upper and lower ends. Returns the integer equivalent of a number by

truncating the fractional part. Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 21 Date Functions Since dates are numeric, calculations can be performed, such as subtraction The TODAY function displays the current date The NOW function displays the current date and time Copyright 2011 Pearson Education, Inc. Publishing as Prentice

Hall. 22 Making Decisions with the IF Function =IF(logical_test, value_if_true,value_if_false) The IF function has three arguments: A logical test or condition that is true or false The resulting value if the condition is true The resulting value if the condition is false Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

23 Using the IF Function Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 24 Designing the Logical Test The logical test is built from the logical operators Operator Description

= Equal to <> Not equal to < Less than > Greater than <=

Less than or equal to >= Greater than or equal to Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 25 Using Functions as Arguments A nested function occurs when one function is embedded as an argument to another function; for example: =IF(A1

Compute the MIN function if A1 is less than A2 Compute the MAX function if A1 is not less than A2 Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 26 Using Lookup Functions Lookup functions are used to look up values in a table to perform calculations or display results For example, a teacher may want to Range up an average Grade

look in order to assign a 90-100 A grade 80-89 B 70-79 C 60-69 D Below 60

F Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 27 Creating a Lookup Table When searching a range, the breakpoint is the lowest value A lookup table typically lists breakpoints in one column and return values in a second column Range Grade

0 F 60 D 70 C 80 B 90

A Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 28 VLOOKUP Function The VLOOKUP function searches a lookup table for a value and returns the result from the related column VLOOKUP has three required arguments: Lookup value Table array (range of lookup table) Column index of return value Copyright 2011 Pearson Education, Inc. Publishing as Prentice

Hall. 29 Using the VLOOKUP Function Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 30 HLOOKUP Function The HLOOKUP function is used when the breakpoints and return data are placed in rows The third

argument now90lists the 0 60 70 80 row index F D C B A Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

31 Calculating Payments with the PMT Function The PMT financial function calculates the periodic payment for a loan with a fixed interest rate and term length PMT has three required arguments: Interest rate Number of periods Present value (amount of loan) Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 32

Using the PMT Function Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 33 Range Names A range name is a word or phrase used to identify a cell or cell range Range names make formulas easier to read Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 34

Range Name Rules Range names use the following rules: 1to 255 characters Begin with a letter or underscore (_) Contain letters, digits, period, underscore Valid names include Rate, Tax_Rate, Rate_2012 Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 35 Creating a Range Name Excel offers a variety of methods to

enter a range name after selecting the cells: Type the range name in the Name Box area Enter the name using New Name dialog box Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 36 Maintaining Range Names Use the Name Manager dialog box to edit or delete a range name Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

37 Summary In this chapter, you have learned to write formulas using relative, absolute, and mixed cell references. You have learned about statistical and date functions, such as SUM, AVERAGE, and TODAY. You have explored the IF, VLOOKUP, and PMT functions. You learned to create and use range names. Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall.

38 Questions Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 39 Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America.

Copyright 2011 Pearson Education, Inc. Publishing as Prentice Hall. 40

Recently Viewed Presentations

  • www.cmiltonwrightmusic.com

    www.cmiltonwrightmusic.com

    Using a variety of techniques, (Groove Pizza, Boom, Xpand!2) you will create/compose a composition using three unique "beats" (drum tracks) that will be connected together by a constant bass line. This project will follow an A-B-A-C-A form.
  • Von Thunen&#x27;s Theories of Land Use and Agricultural Organization

    Von Thunen's Theories of Land Use and Agricultural Organization

    Von Thunen's Theories of Land Use and Agricultural Organization How is agricultural land organized? How does this impact the cost of rent/purchasing land? Land Use and Agriculture Terms Fallow - the practice of farming land, harvesting, tilling (but not planting),...
  • Animal Diversity - Mr. Stanley&#x27;s Classes

    Animal Diversity - Mr. Stanley's Classes

    The parts of a radial animal, such as a sea anemone (phylum Cnidaria), radiate from the center. Any imaginary slice through the central axis divides the animal into mirror images. (a) Symmetry Some animals exhibit bilateral symmetry Or two-sided symmetry...
  • Chapter 4 Skin and Body Membranes Lecture Presentation

    Chapter 4 Skin and Body Membranes Lecture Presentation

    Appendages of the Skin. Sebaceous (oil) glands. Located all over the skin except for palms and soles. Produce sebum (oil) Makes skin soft and moist . Prevents hair from becoming brittle. Kills bacteria. Most have ducts that empty into hair...
  • Protein Expression and Folding Optimization For High-Throughput Proteomics

    Protein Expression and Folding Optimization For High-Throughput Proteomics

    Protein Expression and Folding Optimization For High-Throughput Proteomics Kate Drahos 9 April 2004 It is estimated that 1/3 to 1/2 of prokaryotic proteins cannot be expressed in a soluble form using E. coli expression systems; this estimate most likely increases...
  • Title III Preparing for FPM

    Title III Preparing for FPM

    The academy was planned under a grant from the U. S. Department of Education (USED). However, the content does not necessarily represent the policy of the USED, and you should not assume endorsement by the federal government.
  • Doublethink Is Good

    Doublethink Is Good

    the aggie says asucd is too unselfish "deep throat" is a thoughtcriminal support bush! are your professors un-american liberals? ignorance is strength war is peace freedom is slavery telescreens are plusgood ignorance is strength re-elect bush! a compassionate conservative prevent...
  • Examples of ways to tailor visual supports to

    Examples of ways to tailor visual supports to

    Strategies from the Conscious Discipline program provide choices for children to select methods to greet people. Strategies like these are helpful in increasing engagement and interaction for children who have difficulty with expressive language.