Exploring Microsoft Excel 2016 Comprehensive First Edition Chapter

Exploring: Microsoft® Excel® 2016 Comprehensive First Edition Chapter 2 Formulas and Functions Performing Quantitative Analysis Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objectives • Use Relative, Absolute, and Mixed Cell References in Formulas • Insert a Function • Insert Basic Math and Statistics Functions • Use Date Functions • Determine Results with the IF Function • Use Lookup Functions • Calculate Payments with the PMT function Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objective 1: Use Relative, Absolute, and Mixed Cell References in Formulas Skills: • Use a Relative Cell Reference • Use an Absolute Cell Reference • Use a Mixed Cell Reference Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Use Relative, Absolute, and Mixed Cell References in Formulas (1 of 4) • Relative cell reference—default method of referencing • Absolute cell reference—provides a constant reference to a specific cell • Mixed cell reference—combines absolute and relative cell references • F 4—toggles through relative, absolute, and mixed references Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Use Relative, Absolute, and Mixed Cell References in Formulas (2 of 4) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Use Relative, Absolute, and Mixed Cell References in Formulas (3 of 4) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Use Relative, Absolute, and Mixed Cell References in Formulas (4 of 4) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objective 2: Insert a Function Skills: • Insert a Function Using Formula Auto. Complete • Use the Insert Function Dialog Box Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Insert a Function (1 of 3) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Insert a Function (2 of 3) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Insert a Function (3 of 3) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objective 3: Insert Basic Math and Statistics Functions Skills: • Use the Sum Function • Use the Average and Median Functions • Use the Min and Max Functions • Use the Count Functions • Perform Calculations with Quick Analysis Tools Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Insert Basic Math and Statistics Functions (1 of 2) • =SUM(number 1, [number 2], …) – =SUM(A 1, A 2, A 5) • =AVERAGE(number 1, [number 2], …) – =AVERAGE(B 10: B 20) • =MEDIAN(number 1, [number 2], …) – =MEDIAN(A 2: A 14, F 2: F 14) • =MIN(number 1, [number 2], …) – =MIN(A 1, B 10: C 25) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Insert Basic Math and Statistics Functions (2 of 2) • =MAX(number 1, [number 2], …) – =MAX(A 1, B 10: C 25) • =COUNT(number 1, [number 2], …) – =COUNT(B 10: G 50) • =COUNTBLANK(number 1, [number 2], …) – =COUNTBLANK(B 10: G 50) • =COUNTA(number 1, [number 2], …) – =COUNTA(B 10: G 50) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objective 4: Use Date Functions Skills: • Use the Today Function • Use the Now Function Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Use Date Functions • =TODAY() – Displays a result like: 2/9/2018 • =NOW() – Displays a result like: 2/9/2018 14: 09 Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objective 5: Determine Results with the IF Function Skills: • Use the IF Function Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Determine Results with the IF Function (1 of 2) • =IF(logical_test, [value_if_true], [value_if_false]) – =IF(E 2>B$2, B$3, 0) Operator Description = Equal to <> Not equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Determine Results with the IF Function (2 of 2) • =IF(logical_test, [value_if_true], [value_if_false]) – =IF(E 2>B$2, B$3, 0) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objective 6: Use Lookup Functions Skills: • Use the Vlookup Function • Create the Lookup Table • Use the Hlookup Function Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Use Lookup Functions (1 of 2) • =VLOOKUP(lookup_value, table_array, col_index_num, [ran ge_lookup]) – =VLOOKUP(E 3, $A$3: $B$7, 2) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Use Lookup Functions (2 of 2) • =HLOOKUP(lookup_value, table_array, row_index_num, [ra nge_lookup]) – =HLOOKUP(E 3, $A$3: $B$7, 2) Region Qtr 1 Qtr 2 Qtr 3 Qtr 4 North 3495 4665 4982 5010 South 8044 7692 7812 6252 East 5081 6089 5982 6500 West 4278 4350 4387 7857 Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Objective 7: Calculate Payments with the P MT Function Skills: • Use the PMT Function Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Calculate Payments with the PMT Function • =PMT(rate, nper, pv, [fv], [type]) – =PMT(G 9/$B$5, F 9*$B$5, -D 9) Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Summary • By using Excel formulas and functions, you can perform complicated data analyses • There are three types of cell references—relative, absolute, and mixed • Excel functions include math and statistics, date, logical, lookup, and financial calculations Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Questions Copyright © 2017 Pearson Education, Inc. All Rights Reserved

Copyright © 2017 Pearson Education, Inc. All Rights Reserved