Excel Formulas

Excel Formulas

The Computer Workshop, Inc.

800-639-3535

training@

Lesson Notes

Microsoft Excel Formulas

Rel 6.0, 02/17/2020

Course Number: 0200-240-19-W Course Release Number: 6.0

Software Release Number: 2019

February 24, 2020

Developed by: Brian Ireson, Suzanne Hixon

Edited by: Suzanne Hixon,, Thelma Tippie, and Jeffrey DeRamus

Published by: The Computer Workshop, Inc. 5200 Upper Metro Place, Suite 140

Dublin, Ohio 43017 (614) 798-9505

dba

RoundTown Publishing 5200 Upper Metro Place, Suite 140

Dublin, Ohio 43017

Copyright ? 2020 by RoundTown Publishing. No reproduction or transmittal of any part of this publication, in any form or by any means, mechanical or electronic, including photocopying, recording, storage in an information retrieval system, or otherwise, is permitted without the prior consent of RoundTown Publishing.

Disclaimer: Round Town Publishing produced this manual with great care to make it of good quality and accurate, and therefore, provides no warranties for this publication whatsoever, including, but not limited to, the implied warranties of merchantability or fitness for specific uses. Changes may be made to this document without notice.

Trademark Notices: The Computer Workshop, Inc. and The Computer Workshop logo are registered trademarks of The Computer Workshop, Inc. [Microsoft], [Windows], [PowerPoint], [Excel], [Word], [Word for Windows], and [Works] are registered trademarks of Microsoft Corporation. [InDesign] is a registered trademark of Adobe Systems Incorporated. All other product names and services identified throughout this book are trademarks or registered trademarks of their respective companies. Using any of these trade names is for editorial purposes only and in no way is intended to convey endorsement or other affiliation with this manual.

Table of Contents

Table of Contents..................................................................... ii Using this Manual................................................................... v

To Download Data Files................................................... v Conventions............................................................................ vi

Conventions Used in this Manual................................. vi

Lesson 1: Logical & Lookup Functions

Logical Functions.....................................................................3 Names........................................................................................4

Naming Cells......................................................................4 Editing Names....................................................................4 IF Functions...............................................................................7 IF Functions.........................................................................7 Nested IF Functions.................................................................9 Nested IF Statements.........................................................9 IFS Functions..........................................................................11 IFS Functions....................................................................11 AND Functions.......................................................................13 Using an AND in an IF Function...................................13 OR Functions..........................................................................15 OR Functions....................................................................15 Using an OR in an IF Function.......................................15 NOT Functions.......................................................................17 NOT Functions.................................................................17 Using a NOT Function in an IF Function.....................17 Lookup Functions..................................................................19 VLOOKUP Functions............................................................20 VLOOKUP Function........................................................20 HLOOKUP Function.............................................................24 HLOOKUP Function.......................................................24 Data Validation List...............................................................26 Data Validation List.........................................................26 Creating a Data Validation List......................................26 MATCH Function...................................................................28 MATCH Function.............................................................28 INDEX Function.....................................................................31 INDEX Function...............................................................31 INDEX Array Form..........................................................31 INDEX Reference form....................................................32

Page ii

Excel Formulas, Rel 6.0, 02/17/2020

Table of Contents,

continued

Lesson 2: Complex Summing Functions

Filtering Data for Unique Values.........................................37 Using the Advanced Filter..............................................37

SUMIF Functions....................................................................40 Using the SUMIF Formula..............................................40

AVERAGEIF Function...........................................................43 Using the AVERAGEIF Formula....................................43

COUNTIF Functions..............................................................45 Using the COUNTIF Formula........................................45

SUMIFS Functions.................................................................47 Using the SUMIFS Formula............................................47

COUNTIFS Functions............................................................48 Using the COUNTIFS Formula......................................48

MAXIFS and MINIFS Functions..........................................52

Lesson 3: Date & Time Functions

Time.........................................................................................57 Using the Time Functions...............................................57 Adding and Subtracting Times......................................58 Custom Time Formatting Codes....................................60

NOW Function.......................................................................63 Using the NOW Function...............................................63 Refreshing the NOW Function.......................................63

Dates........................................................................................65 Understanding Dates.......................................................65

Date Functions........................................................................66 Using the TODAY Function............................................66 Finding the Difference Between Two Dates.................66 Using the DATEDIF Function........................................67 Using the Days Formula.................................................69

WORKDAY Functions...........................................................72 Using the WORKDAY Formula.....................................72 Using the EDATE Formula.............................................72

NETWORKDAYS Functions.................................................75 Calculating Working Days.............................................75

Finding the Day of the Week................................................79 Using the WEEKDAY Formula......................................79

SWITCH Function..................................................................81 Using the SWITCH Function..........................................81

Finding Week Number..........................................................83 Using the WeekNum Formula.......................................83 Using the ISOWEEKNUM Formula..............................84

Excel Formulas, Rel 6.0, 02/17/2020

Page iii

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download