Excel 2019 Formulas and Functions Study Guide
()
About this ebook
The Excel 2019 Formulas and Functions Study Guide is a companion book written to complement Excel 2019 Formulas & Functions by M.L. Humphrey.
This guide contains a series of quizzes meant to test a reader's knowledge of how formulas and functions work in Excel as well as approximately a hundred different functions.
The quizzes are presented in an order that mirrors that of Excel 2019 Formulas & Functions.
At the end of the book are ten bonus exercises meant to test a reader's ability to apply Excel functions to real-world scenarios.
M.L. Humphrey
Hi there Sci Fi fans, my name is Maurice Humphrey. I am a Vermont native, husband, father, grandfather, well over 60, Navy veteran, retired IBM engineer, retired printer repairman, Graduated: Goddard Jr. College, VT Technical College, and Trinity College. Over the years I've written technical articles, taught technical classes, and presented at technical conventions. I've been reading science fiction for over 50 years now. First books were "Journey to the Centre of the Earth" by Jules Verne and "The Stars Are Ours" by Andre Norton. I've read and collected many great stories, and a considerable amount of junk ones as well. I'd say by now that I probably have a good idea of what I consider a good story.
Read more from M.L. Humphrey
Excel Tips and Tricks Rating: 0 out of 5 stars0 ratingsExcel 365 Essentials Rating: 0 out of 5 stars0 ratingsHow To Gather And Use Data For Business Analysis Rating: 0 out of 5 stars0 ratingsAccess 2019 Intermediate: Access Essentials 2019 Rating: 0 out of 5 stars0 ratingsAccess 2019 Beginner: Access Essentials 2019 Rating: 0 out of 5 stars0 ratingsAccess Essentials 2019 Rating: 0 out of 5 stars0 ratingsWord 365 Essentials Rating: 0 out of 5 stars0 ratingsWord Essentials 2019 Rating: 0 out of 5 stars0 ratingsMicrosoft Office 2019 Beginner Rating: 0 out of 5 stars0 ratingsWord Essentials Rating: 0 out of 5 stars0 ratingsExcel Essentials 2019 Rating: 0 out of 5 stars0 ratingsPowerPoint 365 Essentials: PowerPoint 365 Essentials Rating: 0 out of 5 stars0 ratingsPowerPoint Essentials Rating: 0 out of 5 stars0 ratingsMicrosoft Office for Beginners Rating: 0 out of 5 stars0 ratingsMicrosoft Office 365 for Beginners Rating: 0 out of 5 stars0 ratingsA Light in the Window Rating: 0 out of 5 stars0 ratingsTails of the North Rating: 0 out of 5 stars0 ratings
Related to Excel 2019 Formulas and Functions Study Guide
Related ebooks
150 Most Poweful Excel Shortcuts: Secrets of Saving Time with MS Excel Rating: 3 out of 5 stars3/5Microsoft Excel Formulas: Master Microsoft Excel 2016 Formulas in 30 days Rating: 4 out of 5 stars4/5Microsoft Excel: Advanced Microsoft Excel Data Analysis for Business Rating: 0 out of 5 stars0 ratingsExcel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables Rating: 5 out of 5 stars5/5Excel 2019 Beginner: Excel Essentials 2019, #1 Rating: 0 out of 5 stars0 ratingsIntermediate Excel: Excel Essentials, #2 Rating: 5 out of 5 stars5/550 Useful Excel Functions: Excel Essentials, #3 Rating: 5 out of 5 stars5/5Mastering Excel Macros: Introduction: Mastering Excel Macros, #1 Rating: 4 out of 5 stars4/5Excel 2019 Intermediate: Excel Essentials 2019, #2 Rating: 0 out of 5 stars0 ratingsExcel VBA: A Beginners' Guide Rating: 4 out of 5 stars4/5Excel for Beginners: Excel Essentials, #1 Rating: 0 out of 5 stars0 ratingsExcel 2019 PivotTables: Easy Excel Essentials 2019, #1 Rating: 5 out of 5 stars5/5Excel : The Ultimate Comprehensive Step-By-Step Guide to the Basics of Excel Programming: 1 Rating: 5 out of 5 stars5/5Excel 2019 Conditional Formatting: Easy Excel Essentials 2019, #3 Rating: 0 out of 5 stars0 ratingsExcel 101: A Beginner's & Intermediate's Guide for Mastering the Quintessence of Microsoft Excel (2010-2019 & 365) in no time! Rating: 0 out of 5 stars0 ratings101 Ready-to-Use Excel Formulas Rating: 4 out of 5 stars4/550 More Excel Functions: Excel Essentials, #4 Rating: 0 out of 5 stars0 ratingsMore Excel Outside the Box: Unbelievable Excel Techniques from Excel MVP Bob Umlas Rating: 0 out of 5 stars0 ratingsExcel :The Ultimate Comprehensive Step-by-Step Guide to Strategies in Excel Programming (Formulas, Shortcuts and Spreadsheets): 2 Rating: 0 out of 5 stars0 ratingsExcel VBA: A Step-By-Step Tutorial For Beginners To Learn Excel VBA Programming From Scratch: 1 Rating: 4 out of 5 stars4/5Excel 2021 Rating: 4 out of 5 stars4/5EXCEL: Microsoft: Boost Your Productivity Quickly! Learn Excel, Spreadsheets, Formulas, Shortcuts, & Macros Rating: 0 out of 5 stars0 ratingsMastering Microsoft Excel 2016: How to Master Microsoft Excel 2016 in 30 days Rating: 5 out of 5 stars5/5Learn Excel Functions: Count, Countif, Sum and Sumif Rating: 5 out of 5 stars5/5
Enterprise Applications For You
Excel : The Ultimate Comprehensive Step-By-Step Guide to the Basics of Excel Programming: 1 Rating: 5 out of 5 stars5/5Excel 101: A Beginner's & Intermediate's Guide for Mastering the Quintessence of Microsoft Excel (2010-2019 & 365) in no time! Rating: 0 out of 5 stars0 ratingsCreating Online Courses with ChatGPT | A Step-by-Step Guide with Prompt Templates Rating: 4 out of 5 stars4/5Excel Formulas That Automate Tasks You No Longer Have Time For Rating: 5 out of 5 stars5/5Bitcoin For Dummies Rating: 4 out of 5 stars4/5QuickBooks 2023 All-in-One For Dummies Rating: 0 out of 5 stars0 ratingsManaging Humans: Biting and Humorous Tales of a Software Engineering Manager Rating: 4 out of 5 stars4/5QuickBooks 2024 All-in-One For Dummies Rating: 0 out of 5 stars0 ratingsCode like a Pro in C# Rating: 0 out of 5 stars0 ratingsExcel 2019 Bible Rating: 5 out of 5 stars5/5Access 2019 For Dummies Rating: 0 out of 5 stars0 ratingsEnterprise AI For Dummies Rating: 3 out of 5 stars3/5Mastering QuickBooks 2020: The ultimate guide to bookkeeping and QuickBooks Online Rating: 0 out of 5 stars0 ratingsExcel Data Analysis For Dummies Rating: 0 out of 5 stars0 ratingsExcel Workbook For Dummies Rating: 4 out of 5 stars4/5Notion for Beginners: Notion for Work, Play, and Productivity Rating: 4 out of 5 stars4/5Scrivener For Dummies Rating: 4 out of 5 stars4/5Excel All-in-One For Dummies Rating: 0 out of 5 stars0 ratingsQuickBooks 2021 For Dummies Rating: 0 out of 5 stars0 ratingsLearning Python Rating: 5 out of 5 stars5/5Excel Formulas and Functions 2020: Excel Academy, #1 Rating: 4 out of 5 stars4/5Dragon NaturallySpeaking For Dummies Rating: 0 out of 5 stars0 ratings
Reviews for Excel 2019 Formulas and Functions Study Guide
0 ratings0 reviews
Book preview
Excel 2019 Formulas and Functions Study Guide - M.L. Humphrey
Quizzes
How Formulas and Functions Work
1. What is the difference between a formula and a function?
2. What starting symbols can you use to indicate to Excel that you are writing a formula?
3. Does a formula have to start with a function?
4. Can a formula use more than one function?
5. What are the basic components of a function?
6. If you enter a formula in Excel and then hit enter, what are you going to see in that cell in your worksheet?
7. If you want to see the actual formula that’s in a cell, how can you do that?
8. If you double-click into a cell with a formula, what will Excel do to any cell references that are part of the formula?
9. How do you leave a cell that has a formula in it when you’ve double-clicked on the cell?
Basic Math Calculations
1. What are the symbols you can use for (a) adding, (b) subtracting, (c) multiplying, and (d) dividing in Excel?
2. What do the following formulas do?
A. =3+2
B. =3-2
C. =3*2
D. =3/2
E. =4+(3*2)
F. =4+3*2
G. =(4+3)*2
H. =2^3
3. What do the following formulas do?
A. =A1+C1
B. =A1-C1
C. =A1*C1
D. =A1/C1
E. =E1+(A1*C1)
F. =E1+A1*C1
G. =(E1+A1)*C1
H. =A1^B1
4. What do examples E, F, and G from the last two questions above demonstrate?
5. What’s a best practice when building a really complex formula in Excel?
6. What does the phrase garbage in, garbage out
mean with respect to Excel formulas?
Where to Find Functions
1. Where can you go in Excel 2019 to look for a function to perform a specific task?
2. What are the categories of functions available in Excel?
3. If you bring up the Insert Function dialogue box and are looking to perform a specific task with a function, where can you search for that?
4. What happens when you click on a function name under Select a Function in the Insert Function dialogue box?
5. If that’s not enough information, what can you do?
6. What happens when you select a function from the Insert Function dialogue box by clicking on it and then clicking on OK?
7. If you already know the function you want to use, but aren’t sure of the inputs or the order they need to be entered in, what can you do from within a cell in your Excel worksheet?
8. If you click on the function name after you’ve typed, =FUNCTION_NAME( what will you get?
9. What if you still can’t figure out what function to use?
Formula and Function Best Practices
1. Name five best practices when working with formulas or functions.
2. Explain what it means to make your assumptions visible.
3. Explain what it means to use paste special-values when you’re done with your calculations and when you should not do this.
4. How can you paste special-values to replace a formula with the result of the formula?
5. Explain why you should store your raw data in one location and work on a copy of that data instead.
6. What’s another best practice when doing a lot of complex work with a dataset that requires multiple steps and manipulations?
7. What’s a good naming convention to use for multiple versions of the same file?
8. Why should you test your formulas before applying them to a large data set?
9. Why can’t you just accept the results Excel gives you? Why should you always gut check
those results?
10. What does it mean to keep your audience in mind?
Copying Formulas
1. What happens when you copy a formula from one cell to another?
2. If you write a formula and you want to fix the reference to a specific cell so that even if the formula is copied elsewhere it continues to reference that cell, how can you do this?
3. What if you just want to lock the row reference but not the entire cell reference?
4. What if you just want to lock the column reference but not the entire cell reference?
5. If you just want to move a formula to a new location without it changing, what’s the best way to do that?
6. What does fixing cell references have to do with formulas that use a cell range, for example, a calculation of the percent of the total for each value in Rows 1 through 25 of Column A.
Top 10 Functions – Part 1
1. What is the function you can use to sum a range of values or a range of cells?
2. How would you write a formula to sum the values in Column A?
3. How would you write a formula to sum the values in Rows 1 through 3?
4. How would you write a formula to sum the values in Columns A, C, and E?
5. How would you write a formula to sum the values in Cells A1 through B6?
6. How would you write a formula to subtract the total of all customer orders in Column C from the amount collected which is listed in Cell F1?
7. What functions can you use to round numbers? Which is the primary function to use? How do they differ?
8. What is the difference between using one of the rounding functions and just formatting a number to display a certain number of decimal places?
9. If you want to round the number 234.561 to the nearest whole number, how would you write that formula?
10. What if you wanted to round it to one decimal place?
11. What if you wanted to round it to two decimal places?
12. What if you wanted to round it to the nearest 100’s (so 200)?
13. If I tell Excel to round a number to the nearest four decimal places, but there are only two decimal places in the number now, what will Excel do?
14. How does Excel decide with the ROUND function whether to round up or to round down?
15. What function will return a random whole number between two values you specify?
16. If I want to return any whole number between 0 and 100, how can I do that?
17. What do you need to be careful of when using the RAND or RANDBETWEEN functions?
18. How can you work around this if you need to capture that value?
19. What is the drawback to doing so?
20. What does the CONVERT function do?
21. Name three categories of conversions CONVERT can do:
22. What happens if you use units from two different categories in the same CONVERT function?
23. Are the units used in the CONVERT function case-sensitive?
24. Write the formula for converting 57 degrees Fahrenheit to Celsius:
25. Write the formula for determining how many days there are in five years using the CONVERT function:
26. Give a likely explanation for why the value returned in the last example is a decimal:
27. Write the formula for converting 60 miles to kilometers.
Top 10 Functions – Part 2
1. What function can you use to join text entries that are