Microsoft Excel Functions and Formulas with Excel 2019/Office 365

474 Pages·2019·53.73 MB·english

by  Bernd Held

M ® E ®
icrosoft xcEl
f
unctions

and
f
orMulas
F E
iFth dition
LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY
By purchasing or using this book (the “Work”), you agree that this license grants
permission to use the contents contained herein, but does not give you the right
of ownership to any of the textual content in the book or ownership to any of the
information or products contained in it. This license does not permit uploading of the
Work onto the Internet or on a network (of any kind) without the written consent of
the Publisher. Duplication or dissemination of any text, code, simulations, images,
etc. contained herein is limited to and subject to licensing terms for the respective
products, and permission must be obtained from the Publisher or the owner of the
content, etc., in order to reproduce or network any portion of the textual material (in
any media) that is contained in the Work.
Mercury Learning and inforMation (“MLI” or “the Publisher”) and anyone involved
in the creation, writing, or production of the companion disc, accompanying algorithms,
code, or computer programs (“the software”), and any accompanying Web site or soft-
ware of the Work, cannot and do not warrant the performance or results that might be
obtained by using the contents of the Work. The author, developers, and the Publisher
have used their best efforts to insure the accuracy and functionality of the textual mate-
rial and/or programs contained in this package; we, however, make no warranty of any
kind, express or implied, regarding the performance of these contents or programs. The
Work is sold “as is” without warranty (except for defective materials used in manufac-
turing the book or due to faulty workmanship).
The author, developers, and the publisher of any accompanying content, and anyone
involved in the composition, production, and manufacturing of this work will not be
liable for damages of any kind arising out of the use of (or the inability to use) the algo-
rithms, source code, computer programs, or textual material contained in this publica-
tion. This includes, but is not limited to, loss of revenue or profit, or other incidental,
physical, or consequential damages arising out of the use of this Work.
The sole remedy in the event of a claim of any kind is expressly limited to replacement
of the book, and only at the discretion of the Publisher. The use of “implied warranty”
and certain “exclusions” vary from state to state, and might not apply to the purchaser
of this product.
Companion files for this text are available for download by writing to the publisher at
[email protected].
M ® E ®
icrosoft xcEl
f
unctions

and
f
orMulas
With Excel 2019 / Office 365
F E
iFth dition
B H
Ernd Eld
B M
rian oriarty
t r
HEodor icHardson
MERCURY LEARNING AND INFORMATION
dullEs, Virginia
Boston, Massachusetts
New Delhi
Copyright © 2019 by Mercury Learning and inforMation LLC.
All rights reserved.
This publication, portions of it, or any accompanying software may not be reproduced in any
way, stored in a retrieval system of any type, or transmitted by any means, media, electronic
display or mechanical display, including, but not limited to, photocopy, recording, Internet
postings, or scanning, without prior permission in writing from the publisher.
Publisher: David Pallai
Mercury Learning and inforMation
22841 Quicksilver Drive
Dulles, VA 20166
[email protected]
www.merclearning.com
[phone]
B. Held, B. Moriarty, and T. Richardson. Microsoft® Excel® Functions and Formulas, Fifth Edition.
ISBN: 978-1-68392-373-2
Microsoft, Excel, Visual Basic, and Windows are registered trademarks of Microsoft
Corporation in the U.S. and other countries.
The publisher recognizes and respects all marks used by companies, manufacturers, and
developers as a means to distinguish their products. All brand names and product names
mentioned in this book are trademarks or service marks of their respective companies. Any
omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to
infringe on the property of others.
Library of Congress Control Number: 2019931784
192021321 This book is printed on acid-free paper in the United States of America.
Our titles are available for adoption, license, or bulk purchase by institutions, corporations,
etc. Digital versions of this title are available at www.academiccourseware.com and most
digital vendors. Companion disc files are available for downloading by contacting info@
merclearning.com. For additional information, please contact the Customer Service Dept. at
[phone] (toll free).
The sole obligation of Mercury Learning and inforMation to the purchaser is to replace the
disc, based on defective materials or faulty workmanship, but not based on the operation or
functionality of the product.
CONTENTS
Acknowledgments xvii
Introduction xix
Chapter 1 : Formulas in Excel 1
Calculate production per hour 1
Calculate the age of a person in days 2
Calculate a price reduction 3
Convert currency 4
Convert from hours to minutes 5
Determine fuel consumption 6
Calculate your ideal and recommended weights 7
The quick calendar 9
Design your own to-do list 10
Increment row numbers 11
Convert negative values to positive 12
Calculate sales taxes 13
Combine text and numbers 14
Combine text and date 15
Combine text and time 16
Generate a special ranking list 16
Determine average output 18
Determine stock gains and losses 19
Evaluate profitability 20
Determine percentage of completion 21
Convert miles per hour to kilometers per hour 22
Convert feet per minute to meters per second 23
Convert liters to barrels, gallons, quarts, and pints 24
vi • Microsoft Excel Functions and Formulas, Fifth Edition
Convert from Fahrenheit to Celsius 25
Convert from Celsius to Fahrenheit 26
Calculate total with percentage 27
Monitor the daily production plan 28
Calculate the number of hours between two dates 29
Determine the price per pound 31
Determine how many pieces to put in a box 32
Calculate the number of employees required for a project 33
Distribute sales 35
Calculate your net income 36
Calculate the percentage of price reduction 37
Divide and double every three hours 38
Calculate the average speed 39
Calculate number of characters in a string 40
Chapter 2 : Logical Functions 43
Use the AND function to compare two columns 43
Use the AND function to show sales for a specific period of time 44
Use the OR function to check cells for text 45
Use the OR function to check cells for numbers 46
Use the XOR function to check for mutually exclusive conditions 47
Use the IF function to compare columns and return a specific result 48
Use the IF function to check for larger, equivalent, or smaller values 49
Combine IF with AND to check several conditions 50
Use the IF function to determine the quarter of a year 51
Use the IF function to check cells in worksheets and workbooks 52
Use the IF function to calculate with different tax rates 52
Use the IF function to calculate the commissions for individual sales 53
Use the IFS function to calculate the commissions for
individual sales (*NEW IN EXCEL 2016*) 54
Use the IF function to compare two cells 55
Use the IFS function to compare two cells
(*NEW IN EXCEL 2016*) 56
Use the SWITCH function to compare two cells
(*NEW IN EXCEL 2016*) 57
Use the INT function with the IF function 58
Use the TYPE function to check for invalid values 59
Use nested IF functions to cover multiple possibilities 60
Use IFS function to cover multiple possibilities
(*NEW IN EXCEL 2016*) 61
Use SWITCH function to cover multiple possibilities
(*NEW IN EXCEL 2016*) 62
Contents • vii
Use the IF function to check whether a date is in the past or the future 63
Use the IF function to create your own timesheet 64
Use the IFERROR function to display a default 66
Chapter 3 : Text Functions 69
Use the LEFT and RIGHT functions to separate a text string of numbers 69
Use the LEFT function to convert invalid numbers to valid numbers 70
Use the SEARCH function to separate first name from last name 71
Use the MID function to separate last name from first name 72
Use the MID function to sum the digits of a number 74
Use the EXACT function to compare two columns 75
Use the SUBSTITUTE function to substitute characters 76
Use the SUBSTITUTE function to substitute parts
of a cell 77
Use the SUBSTITUTE function to convert numbers to words 78
Use the SUBSTITUTE function to remove word wrapping in cells 79
Use the SUBSTITUTE function to combine and separate columns 80
Use the REPLACE function to replace and calculate 81
Use the FIND function to combine text and date 83
Use the UPPER function to convert text from lowercase to uppercase 85
Use the LOWER function to convert text from uppercase to lowercase 86
Use the PROPER function to convert initial characters from
lowercase to uppercase 87
Use the FIXED function to round and convert numbers to text 88
Use the TRIM function to delete spaces 89
Use the TRIM function to convert “text-numbers” to real numbers 90
Use the CLEAN function to remove all non-printable characters 91
Use the REPT function to show data in graphic mode 91
Use the REPT function to show data in a chart 92
Use the CHAR function to check your fonts 93
Use the CHAR function to determine special characters 94
Use the CODE function to determine the numeric code of a character 94
Use the UNICHAR function to determine the Unicode character
from a number 95
Use the UNICODE function to determine the numeric Unicode
value of a character 96
Use the DOLLAR function to convert numbers to currency in text format 97
Use the T function to check for valid numbers 98
Use the TEXT function to combine and format text 99
Use CONCATENATE function to combine text 101
Use CONCAT function to combine text 102
Use TEXTJOIN function to combine text 104
viii • Microsoft Excel Functions and Formulas, Fifth Edition
Chapter 4 : Date and Time Functions 107
Use custom formatting to display the day of the week 107
Use the WEEKDAY function to determine the weekend 108
Use the TODAY function to check for future dates 109
Use the TEXT function to calculate with the TODAY function 110
Use the NOW function to show the current time 111
Use the NOW function to calculate time 112
Use the DATE function to combine columns with date parts 113
Use the LEFT, MID, and RIGHT functions to extract date parts 114
Use the TEXT function to extract date parts 115
Use the DATEVALUE function to recalculate dates formatted as text 116
Use the YEAR function to extract the year part of a date 117
Use the MONTH function to extract the month part of a date 118
Use the DAY function to extract the day part of a date 119
Use the MONTH and DAY functions to sort birthdays by month 120
Use the DATE function to add months to a date 121
Use the EOMONTH function to determine the last day of a month 122
Use the DAYS360 function to calculate with a 360-day year 123
Use the WEEKDAY function to calculate with different hourly pay rates 124
Use the WEEKNUM function to determine the week number 125
Use the EDATE function to calculate months 126
Use the WORKDAY function to calculate workdays 127
Use the NETWORKDAYS function to determine the number of workdays 129
Use the YEARFRAC function to calculate ages of employees 130
Use the DATEDIF function to calculate ages of employees 131
Use the WEEKDAY function to calculate the weeks of Advent 133
Use the TIMEVALUE function to convert text to time 134
Use a custom format to create a time format 135
Use the HOUR function to calculate with 100-minute hours 136
Use the TIME function to combine single time parts 137
Chapter 5 : Basic Statistical Functions 139
Use the MAX function to determine the largest value in a range 139
Use the MIN function to discover the lowest sales volume for a month 140
Use the MINIFS function to discover the lowest sales volume for
a month based on criteria 141
Use the MAXIFS function to discover the highest sales volume
for a month based on criteria 142
Use the MIN function to detect the smallest value in a column 144
Use the SMALL function to find the smallest values in a list 145
Use the LARGE function to find the highest values 146
Contents • ix
Use the INDEX, MATCH, and LARGE functions to determine
and locate the best salesperson 147
Use the SMALL function to compare prices and select the cheapest offer 148
Use the AVERAGE function to calculate the average output 149
Use the SUBTOTAL function to sum a filtered list 150
Use the COUNT function to count cells containing numeric data 152
Use the COUNTA function to count cells containing data 153
Use the COUNTA function to count cells containing text 154
Use the COUNTBLANK function to count empty cells 155
Use the COUNTA function to determine the last filled row 156
Use the SUBTOTAL function to count rows in filtered lists 157
Use the RANK function to determine the rank of sales 158
Use the MEDIAN function to calculate the median sales 159
Use the QUARTILE function to calculate the quartiles 160
Use the STDEV function to determine the standard deviation 161
Use the FORECAST.LINEAR function to determine future values 162
Use the FORECAST.ETS function to determine future values 164
Use the FORECAST.ETS.CONFINT function to determine
confidence in future values 165
Use the FORECAST.ETS.SEASONALITY function to future value patterns 167
Use the CORREL function to determine data correlation 168
Chapter 6 : Mathematical Functions 171
Use the SUM function to sum a range 171
Use the SUM function to sum several ranges 172
Use the SUMIF function to determine sales of a team 173
Use the SUMIF function to sum costs higher than $1,000 174
Use the SUMIF function to sum costs up to a certain date 175
Use the COUNTIF function to count phases that cost more than $1,000 176
Use the COUNTIF function to calculate an attendance list 178
Use the SUMPRODUCT function to calculate the value of the inventory 179
Use the SUMPRODUCT function to sum sales of a team 180
Use the SUMPRODUCT function to multiply and sum at the same time 181
Use the ROUND function to round numbers 182
Use the ROUNDDOWN function to round numbers down 183
Use the ROUNDUP function to round numbers up 184
Use the ROUND function to round time values to whole minutes 185
Use the ROUND function to round time values to whole hours 186
Use the MROUND function to round prices to 5 or 25 cents 187
Use the MROUND function to round values to the nearest
multiple of 10 or 50 188
Use the CEILING function to round up prices to the nearest $100 190