by robert grauer, keith mulbery, insert and mary anne ...liush/bdss/exp2010_e07_ppts.pdf · •...
TRANSCRIPT
INSERT BOOK COVER
1Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall.
Exploring Microsoft Office Excel 2010by Robert Grauer, Keith Mulbery, and Mary Anne Poatsy
Chapter 7Specialized Functions
Objectives
• Use conditional math and statistical functions• Calculate relative standing with statistical functions• Create a nested logical function• Use MATCH and INDEX lookup functions• Use advanced filtering• Manipulate data with database functions• Create a loan amortization table• Perform other financial calculations
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 2
Use Conditional Math and Statistical Functions
• With Excel’s math and statistical functions, you can:– Perform conditional calculations– Indicate relative standing
• When one condition must be met use:– SUMIF– AVERAGEIF– COUNTIF
• The above functions require a:– Range argument to specify the range of cells to evaluate– Criteria argument that specifies the condition
• The SUMIF and AVERAGEIF functions also contain an optional argument to specify the range of values to sum or average
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 3
Use Conditional Math and Statistical Functions
• When more than one condition must be met use:– SUMIFS– AVERAGEIFS– COUNTIFS
• COUNTIFS contains at lest four arguments (two ranges and their respective criteria)
• SUMIFS and AVERSGEIFS contain at least five arguments with the additional argument that specifies the range to sum or average being first
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 4
Use Conditional Math and Statistical Functions
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 5
Use Conditional Math and Statistical Functions
• To enter SUMIF and SUMIFS:–On the Formulas tab, click Math & Trig
in the Function Library group– Select the desired function–Enter the arguments in the Functions
dialog box–Click OK
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 6
Use Conditional Math and Statistical Functions
• To enter AVERAGEIF, AVERAGEIFS, COUNTIF, and COUNTIFS:– On the Formulas tab, click More Functions in the
Function Library group– Point to Statistical and select the desired function– Enter the arguments in the Functions dialog box– Click OK
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 7
Calculate Relative Standing With Statistical Functions
Excel contains four sets of statistical functions to indicate relative standing:
– RANK.EQ and RANK.AVG calculate ranking for individual values within a list
– PERCENTRANK.INC and PERCENTRANK.EXC calculate rank as a percentage for each value in a list
– QUARTILE.INC and QUARTILE.EXC identify the value at a specific quartile
– PERCENTILE.INC and PERCENTILE.EXC identify the kth percentile of a value
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 8
Calculate Relative Standing With Statistical Functions
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 9
Calculate Relative Standing With Statistical Functions
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 10
Calculate Relative Standing With Statistical Functions
• Analysis ToolPak ─ an add-in that contains tools for performing complex statistical analyses such as:– ANOVA– Correlation– F-tests
• To use one of the data-analysis tools from the add in:– On the Data tab, click Data Analysis in the Analysis
group– Select the desired tool, and then click OK– Enter the parameters, and then click OK
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 11
Create a Nested Logical Function
• A nested IF function ─ one that contains one or more additional IF functions nested inside one or more arguments
• Excel permits up to 64 IF statements in one formula
• You can nest the AND, OR, or NOT logical functions
• The AND and OR functions allows you to test up to 255 conditions
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 12
Create a Nested Logical Function
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 13
Use MATCH and INDEX Lookup Functions
• The MATCH function ─ identifies a search item’s position in a list
• The INDEX function ─ returns a value or reference to a value within a range
• Nest the MATCH function inside the INDEX function to identify a location and then return related data
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 14
Use MATCH and INDEX Lookup Functions
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 15
Use MATCH and INDEX Lookup Functions
• To nest functions in other functions:1. Click Insert Function, select the outer function, and
then click OK2. Click in the argument box where the nested function is
needed, click the Name Box arrow, and then select the desired function, and then click OK
3. Enter the arguments for the nested function; click in the outer function’s name in the Formula Bar to display the Function Arguments dialog box for the outer function again
4. Continuing entering or nesting other arguments5. When the entire function is complete, click OK
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 16
Use Advanced Filtering
• Use advanced filtering to keep the original dataset visible and extract a copy of data that meet certain criteria in another location:1. Create a separate criteria range that contains
column labels and lists the conditions2. Create duplicate labels for an output range in
an area that contains sufficient empty rows
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 17
Use Advanced Filtering
• To apply the advanced filter and copy the selected records to another area of the worksheet:1. Click in a cell in the data table2. On the Data tab, click Advanced in the Sort & Filter
group3. Click Copy to another location4. Enter the criteria range, including the criteria labels, in
the Criteria range box5. Specify the Copy to range6. Click OK
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 18
Use Advanced Filtering
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 19
Manipulate Data with Database Functions
• Database functions calculate aggregates for databases that have advanced filter(s)
• Some of the most common database functions:– DSUM– DAVERAGE– DMIN– DMAX– DCOUNT
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 20
Manipulate Data with Database Functions
• Database functions have three arguments:1. Database ─ the entire table including column labels2. Field ─ indicates which column is used in the function
a. Enter the column label in quotation marks or as a number that represents the position of the column in the table
3. Criteria ─ the range of cells that contains the conditions you specify
a. It includes at least one column label and at least one cell below the column label that specifies the condition
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 21
Manipulate Data with Database Functions
• To insert a database function:– Click Insert function in the Function
Library group– Click the Or select a category arrow– Select Database– Click the desired function
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 22
Manipulate Data with Database Functions
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 23
Create a Loan Amortization Table
• A loan amortization table ─ a schedule of monthly payments, interest per period, principal repayment per period, and balances
• Specialized financial functions include:– IPMT– PPMT– CUMIPMT– CUMPRINC
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 24
Create a Loan Amortization Table
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 25
Perform Other Financial Calculations
• Other financial functions:– PV: calculates the present value of an investment– FV: calculates the future value of an investment– NPV: calculates the net present value of an
investment with given cash flows and a discount rate– NPER: calculates the number of payment periods
for a loan or an investment– RATE: calculates the periodic rate for an investment
or loan
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 26
Perform Other Financial Calculations
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 27
Summary
• In this chapter, you learned numerous functions from the following categories: – Math & Trig– Logical, Lookup & Reference– Statistical, Database, and Financial
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 28
Questions
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 29
Copyright
Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 30
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.