Excel 2019 Formulas and Functions Study Guide

By M.L. Humphrey

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.

• Language: English
• Publisher: M.L. Humphrey
• Release date: Feb 16, 2021
• ISBN: 9781393086246

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.

Book preview

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