Exploring Microsoft Excel 2016 Series Editor Mary Anne

  • Slides: 36
Download presentation
Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr.

Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr. Robert T. Grauer

Chapter 7 Specialized Functions Using Date, Logical, Lookup, Database, and Financial Functions Copyright ©

Chapter 7 Specialized Functions Using Date, Logical, Lookup, Database, and Financial Functions Copyright © 2017 Pearson Education, Inc.

Objectives • Use Date Functions • Create a Nested Logical Function • Use Advanced

Objectives • Use Date Functions • Create a Nested Logical Function • Use Advanced Lookup Functions • Apply Advanced Filtering • Manipulate Data with Database Functions • Use Financial Functions • Create a Loan Amortization Table Copyright © 2017 Pearson Education, Inc.

Objective 1: Use Date Functions Skills: Use the YEARFRAC Function Use the DAYS Function

Objective 1: Use Date Functions Skills: Use the YEARFRAC Function Use the DAYS Function Use the DATE, YEAR, and MONTH Functions Use Other Date Functions Copyright © 2017 Pearson Education, Inc.

Use Date Functions • Excel Time & Date functions: §DAYS—calculates the number of days

Use Date Functions • Excel Time & Date functions: §DAYS—calculates the number of days between two dates =DAYS(end_date, start_date) §YEARFRAC—calculates the fraction of a year between two dates =YEARFRAC(start_date, end_date) Copyright © 2017 Pearson Education, Inc.

Use Date Functions • Excel Time & Date functions: §DAY—displays the day (1– 31)

Use Date Functions • Excel Time & Date functions: §DAY—displays the day (1– 31) =DAY(serial_number) §MONTH—displays the month (1– 12) =MONTH(serial_number) §YEAR—displays the year (2018) =YEAR(serial_number) Copyright © 2017 Pearson Education, Inc.

Objective 2: Create a Nested Logical Function Skills: Create a Nested IF Function Use

Objective 2: Create a Nested Logical Function Skills: Create a Nested IF Function Use the AND Function Nest an OR or NOT Function Copyright © 2017 Pearson Education, Inc.

Create a Nested Logical Function Copyright © 2017 Pearson Education, Inc.

Create a Nested Logical Function Copyright © 2017 Pearson Education, Inc.

Create a Nested Logical Function =IF(C 7<C$2, D 7*D$2, IF(C 7<=C$3, D 7*D$4)) Copyright

Create a Nested Logical Function =IF(C 7<C$2, D 7*D$2, IF(C 7<=C$3, D 7*D$4)) Copyright © 2017 Pearson Education, Inc.

Create a Nested Logical Function • Nesting AND, OR, or NOT functions § AND—evaluates

Create a Nested Logical Function • Nesting AND, OR, or NOT functions § AND—evaluates to TRUE if all conditions are true =AND(logical 1, logical 2, logical 3, …) § OR—evaluates to TRUE if any of the conditions are true =OR(logical 1, logical 2, logical 3, …) § NOT—reverses the truth of a logical test =NOT(logical) Copyright © 2017 Pearson Education, Inc.

Create a Nested Logical Function AND function Copyright © 2017 Pearson Education, Inc.

Create a Nested Logical Function AND function Copyright © 2017 Pearson Education, Inc.

Objective 3: Use Advanced Lookup Functions • Skills: Create a Lookup Field Use the

Objective 3: Use Advanced Lookup Functions • Skills: Create a Lookup Field Use the INDEX Function Use the MATCH Function Copyright © 2017 Pearson Education, Inc.

Use Advanced Lookup Functions • Additional lookup functions: § INDEX—returns a value at the

Use Advanced Lookup Functions • Additional lookup functions: § INDEX—returns a value at the intersection of a specified row and column =INDEX(array, row_num, [column_num]) Copyright © 2017 Pearson Education, Inc.

Use Advanced Lookup Functions • Additional lookup functions: § MATCH—searches through a range for

Use Advanced Lookup Functions • Additional lookup functions: § MATCH—searches through a range for a specific value and returns the relative position =MATCH(lookup_value, lookup_array, [match_type]) Copyright © 2017 Pearson Education, Inc.

Objective 4: Apply Advanced Filtering Skills: Create Criteria and Output Ranges Apply an Advanced

Objective 4: Apply Advanced Filtering Skills: Create Criteria and Output Ranges Apply an Advanced Filter Copyright © 2017 Pearson Education, Inc.

Apply Advanced Filtering Original dataset Criteria Filtered and copied results Copyright © 2017 Pearson

Apply Advanced Filtering Original dataset Criteria Filtered and copied results Copyright © 2017 Pearson Education, Inc.

Apply Advanced Filtering Criteria Filtered and copied results Copyright © 2017 Pearson Education, Inc.

Apply Advanced Filtering Criteria Filtered and copied results Copyright © 2017 Pearson Education, Inc.

Apply Advanced Filtering New location $A$26: $E$26 List range including headings Criteria range including

Apply Advanced Filtering New location $A$26: $E$26 List range including headings Criteria range including labels Copyright © 2017 Pearson Education, Inc.

Objective 5: Manipulate Data with Database Functions Skills: Use the DSUM Function Use the

Objective 5: Manipulate Data with Database Functions Skills: Use the DSUM Function Use the DAVERAGE Function Use the DMIN Function Use the DMAX Function Use the DCOUNT Function Copyright © 2017 Pearson Education, Inc.

Manipulate Data with Database Functions • Excel Database function arguments: § Database—the entire dataset

Manipulate Data with Database Functions • Excel Database function arguments: § Database—the entire dataset § Field—the column that contains the values operated on by the function § Criteria—defines the conditions to be met by the function Copyright © 2017 Pearson Education, Inc.

Manipulate Data with Database Functions • Excel Database functions: §DSUM—adds the values that match

Manipulate Data with Database Functions • Excel Database functions: §DSUM—adds the values that match specified conditions =DSUM(database, field, criteria) §DAVERAGE—determines the average of values that match specified conditions =DAVERAGE(database, field, criteria) §DMAX—identifies the highest value that match specified conditions =DMAX(database, field, criteria) Copyright © 2017 Pearson Education, Inc.

Manipulate Data with Database Functions • Excel Database functions: §DMIN—identifies the lowest value that

Manipulate Data with Database Functions • Excel Database functions: §DMIN—identifies the lowest value that match specified conditions =DMIN(database, field, criteria) § DCOUNT—counts the cells that contain numbers that match specified conditions =DCOUNT(database, field, criteria) Copyright © 2017 Pearson Education, Inc.

Manipulate Data with Database Functions DSUM function Copyright © 2017 Pearson Education, Inc.

Manipulate Data with Database Functions DSUM function Copyright © 2017 Pearson Education, Inc.

Objective 6: Use Financial Functions Skills: Use the PV Function Use the FV Function

Objective 6: Use Financial Functions Skills: Use the PV Function Use the FV Function Use the NPER Function Use the RATE Function Copyright © 2017 Pearson Education, Inc.

Use Financial Functions • Excel financial functions: §PV—calculates the current value of an investment

Use Financial Functions • Excel financial functions: §PV—calculates the current value of an investment with a fixed rate, number of payments, and payments =PV(rate, nper, pmt, [fv], [type]) §FV—calculates the future value of an investment, given a fixed interest rate, term, and payments =FV(rate, nper, pmt, [pv], [type]) § NPV—calculates the net present value of an investment, given a fixed rate and future payments that may be identical or different =NPV(rate, value 1, value 2, ) Copyright © 2017 Pearson Education, Inc.

Use Financial Functions Copyright © 2017 Pearson Education, Inc.

Use Financial Functions Copyright © 2017 Pearson Education, Inc.

Use Financial Functions • Excel financial functions: §NPER—calculates the number of payments for an

Use Financial Functions • Excel financial functions: §NPER—calculates the number of payments for an investment or loan given a fixed interest rate, periodic payment, and present value =NPER(rate, pmt, pv, [fv], [type]) § RATE—calculates the periodic rate for an investment or loan given the number of payment periods, payments, and present value =RATE(nper, pmt, pv, [fv], [type]) Copyright © 2017 Pearson Education, Inc.

Use Financial Functions Copyright © 2017 Pearson Education, Inc.

Use Financial Functions Copyright © 2017 Pearson Education, Inc.

Objective 7: Create a Loan Amortization Table Skills: Enter Formulas in the Amortization Table

Objective 7: Create a Loan Amortization Table Skills: Enter Formulas in the Amortization Table Use the IPMT Function Use the PPMT Function Use the CUMIPMT Function Use the CUMPRINC Function Copyright © 2017 Pearson Education, Inc.

Create a Loan Amortization Table Copyright © 2017 Pearson Education, Inc.

Create a Loan Amortization Table Copyright © 2017 Pearson Education, Inc.

Create a Loan Amortization Table • Additional financial functions: §IPMT—calculates the periodic interest for

Create a Loan Amortization Table • Additional financial functions: §IPMT—calculates the periodic interest for payment period on a loan or an investment given a fixed interest rate, term, and payments =IPMT(rate, per, nper, pv, [fv], [type]) § PPMT—calculates the principal payment for payment period on a loan or an investment given a fixed interest rate, term, and payments =PPMT(rate, per, nper, pv, [fv], [type]) Copyright © 2017 Pearson Education, Inc.

Create a Loan Amortization Table • Additional financial functions: §CUMIPMT—calculates the cumulative interest through

Create a Loan Amortization Table • Additional financial functions: §CUMIPMT—calculates the cumulative interest through a specified payment period =CUMIPMT(rate, nper, pv, start_period, end_period, type) § CUMPRINC—calculates the cumulative principal through a specified payment period =CUMPRINC(rate, nper, pv, start_period, end_period, type) Copyright © 2017 Pearson Education, Inc.

Create a Loan Amortization Table Copyright © 2017 Pearson Education, Inc.

Create a Loan Amortization Table Copyright © 2017 Pearson Education, Inc.

Summary Additional Excel functions: • Date, logical, and lookup: § Date, DAY, MONTH, and

Summary Additional Excel functions: • Date, logical, and lookup: § Date, DAY, MONTH, and YEAR § IF, AND, OR, and NOT § INDEX and MATCH • Database: §DSUM, DAVERAGE, DMIN & DMAX, and DCOUNT • Financial: § PV, FV, NPER, RATE, IPMT, PPMT, CUMIPMT, and CUMPRINC Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Copyright All rights reserved. No part of this publication may be reproduced, stored in

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 © 2017 Pearson Education, Inc.