Exploring Microsoft Office 2016 Series Editor Mary Anne

Exploring Excel 2016 Chapter 2 Formulas and Functions Performing Quantitative Analysis Copyright © 2017

Objectives • Use Relative, Absolute, and Mixed Cell References in Formulas • Insert a

Objective 1: Use Relative, Absolute, and Mixed Cell References in Formulas (1 of 5)

Use Relative, Absolute, and Mixed Cell References in Formulas (2 of 5) • Relative

Use Relative, Absolute, and Mixed Cell References in Formulas (3 of 5) G 2

Use Relative, Absolute, and Mixed Cell References in Formulas (4 of 5) E 3

Use Relative, Absolute, and Mixed Cell References in Formulas (5 of 5) E 2

Objective 2: Insert a Function (1 of 4) Skills: • Insert a Function Using

Insert a Function (2 of 4) Auto. Complete based on =SU Screen. Tip based

Insert a Function (3 of 4) Search Sum selected Category Sum syntax and description

Insert a Function (4 of 4) Function Selected cell Argument Copyright © 2017 Pearson

Objective 3: Insert Basic Math and Statistics Functions (1 of 3) Skills: • Use

Insert Basic Math and Statistics Functions (2 of 3) • =SUM(number 1, [number 2],

Insert Basic Math and Statistics Functions (3 of 3) • =MAX(number 1, [number 2],

Objective 4: Use Date Functions (1 of 2) Skills: • Use the TODAY Function

Use Date Functions (2 of 2) • =TODAY() • Displays a result like: 2/9/2018

Objective 5: Determine Results with the IF Function (1 of 3) Skills: • Use

Determine Results with the IF Function (2 of 3) • =IF(logical_test, [value_if_true], [value_if_false]) •

Determine Results with the IF Function (3 of 3) • =IF(logical_test, [value_if_true], [value_if_false]) •

Objective 6: Use Lookup Functions (1 of 3) Skills: • Use the VLOOKUP Function

Use Lookup Functions (2 of 3) • =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) • =VLOOKUP(E 3,

Use Lookup Functions (3 of 3) • =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) • =HLOOKUP(E 3,

Objective 7: Calculate Payments with the PMT Function (1 of 2) Skills: • Use

Calculate Payments with the PMT Function (2 of 2) • =PMT(rate, nper, pv, [fv],

Summary • By using Excel formulas and functions, you can perform complicated data analyses

Questions ? Copyright © 2017 Pearson Education, Inc.

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

Slides: 28

Download presentation

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.

Objective 1: Use Relative, Absolute, and Mixed Cell References in Formulas (1 of 5) Skills: • Use a Relative Cell Reference • Use an Absolute Cell Reference • Use a Mixed Cell Reference Copyright © 2017 Pearson Education, Inc.

Use Relative, Absolute, and Mixed Cell References in Formulas (2 of 5) • 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.

Objective 3: Insert Basic Math and Statistics Functions (1 of 3) 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.

Insert Basic Math and Statistics Functions (2 of 3) • =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.

Insert Basic Math and Statistics Functions (3 of 3) • =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.

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.

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.