Excel 2019 Formulas & Functions: Excel Essentials 2019, #3

By M.L. Humphrey

In this final book of the Excel Essentials 2019 series, Excel 2019 Formulas and Functions, M.L. Humphrey takes a deeper dive into how formulas and functions work in Microsoft Excel.

 

Learn the difference between formulas and functions and how to find any function you need in Excel as well as how to troubleshoot common issues with formulas and functions.

 

Approximately sixty functions are covered in detail, including functions like IFS and TEXTJOIN that were added for Excel 2019. In total, over a hundred functions are covered in this book.

 

By the time you're done you should be comfortable working with formulas and functions in Excel, know how to use the most common functions, and know how to find others when you need them.

• Language: English
• Publisher: M.L. Humphrey
• Release date: Jan 4, 2021
• ISBN: 9781393415275

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

HOW FORMULAS AND FUNCTIONS WORK

For purposes of this book, we’re going to define a formula in Excel as anything that is started with an equals sign and asks Excel to perform a calculation or task.

(Technically, as discussed in Excel 2019 Beginner you can start a formula with a plus or a minus sign as well, but I’m just going to ignore that because unless you’re coming from a specific background where you learned to do things that way, you shouldn’t do that. Also, Excel transforms those formulas into ones that use an equals sign anyway.)

I’m going to define a function as a command that is used within a formula to give instructions to Excel to perform a pre-defined task or set of tasks.

A function is basically agreed-upon shorthand.

So a formula in Excel could be as basic as:

=A1

It starts with an equals sign and is telling Excel that this particular cell where we’ve written our formula should have the exact same value as Cell A1. The task Excel completes here is pulling in that value.

But usually a formula will be more complex than that.

Look at the example I gave you above with SUMPRODUCT. (A function so I write it in capitals. All functions in this book will be written with capital letters.)

=SUMPRODUCT(A1:A100,B1:B100)

Still a formula, because we started with the equals sign and are asking Excel to perform a task. But in this case we’ve used the function SUMPRODUCT as shorthand to tell Excel that for every row in that range it should take the value from Column A and multiply it by the value in Column B and then should sum the resulting values and return the total.

Formulas can actually combine a large number of functions and calculations. For example the following formula combines two functions, TRIM and CONCAT, as well as four cell references, and four fixed values that indicate spaces:

=TRIM(CONCAT(A2, ,B2, ,C2, ,D2))

This is still a fairly simple formula. It can get much more complex. But as long as you can build the formula in the right way, Excel will perform all of the tasks you assign it.

The basic rules of building a formula are start with an equals sign and if you needed to use an opening paren make sure that it’s paired with a closing paren.

All functions require the use of parens. You write the function name, the next thing you include is that opening paren, then you tell Excel the information it needs to perform that function (which varies by function), and then you end with a closing paren.

As you can see above with CONCAT, a function does not have to start a formula. It can and often will, but that is not a requirement.

For example, this is a perfectly legitimate formula that uses a function but starts with a cell reference instead:

=A1+SUM(B1:B5)

After you enter your formula in your cell, hit enter. (Or leave the cell by arrowing, using the tab key, or clicking away.)

The cell will then display the result of the formula. The formula will remain visible via the formula bar.

To see the formula that was used in a cell (if any), you can click on the cell and look to the formula bar.

Like so where I’ve clicked on Cell C1 which contains the formula =A1+B1:

Formula bar example

You can also double-click into the cell itself (Cell C1 here) and the cell will display the formula and also highlight any cells used in the formula. Like so:

Example of formula with double-click to show included cells

It’s a little hard to see in print, but Cells A1 and B1 are highlighted in colors that match the color shown for their cell references in the formula in Cell C1. This makes it easy to see which cell is being used in which part of a formula, which becomes especially helpful when dealing with very complex formulas.

If you double-click on a cell to see the formula in the cell, exit the cell using Esc, Enter, tab or by clicking away. Do not arrow away because Excel may try to select a new range of cells to use in the formula instead of leaving the cell.

When you exit a cell with a formula, the cell will return to showing the calculated value.

With either method, the formula bar will always contain the formula for the cell you have selected.

Okay. Next we’re going to take a quick detour into basic math calculations that don’t require any functions at all, but are just simple formulas.

BASIC MATH CALCULATIONS

Alright. Now let’s take a quick detour with formulas before we go further with functions and walk through some basic math calculations that you can do without using a single function.

If I want to add two numerical values together in Excel, I could just go to any cell and type the formula into a cell using the plus sign (+) to indicate addition.

So here I’m adding 2 to 3:

=2+3

If those values were already showing in other cells, let’s say Cells A1 and B1, I could write the formula to reference those cells instead:

=A1+B1

(If you aren’t familiar with cell notation in Excel, see Appendix A.)

If I use cell notation, like in the second example there, then any change I make to the values in Cells A1 or B1 will also change the result of my formula because my formula is no longer performing a fixed calculation, like 2+3, but is instead performing a conditional calculation based on what’s in Cells A1 and B1.

To subtract one number from another you use the minus (-) sign. To multiply two numbers you use the asterisk (*) sign. To divide two numbers you use the forward slash (/). So:

=3-2 would subtract 2 from 3

---

=3*2 would multiply 3 times 2

---

=3/2 would divide 3 by 2

As I mentioned above, your formulas can either use cell references or numbers. So:

=A1-B1 would subtract the value in Cell B1 from the value in Cell A1

---

=A1*B1 would multiply the value in Cell A1 by the value in Cell B1

---

=A1/B1 would divide the value in Cell A1 by the value in Cell B1

Those are the most common non-function operators in Excel, but you can also use others such as the caret (^) symbol to indicate taking a value to a power. So:

=2^2 would be 2 times 2

---

=4^.5 would take the square root of 4

---

=3^3 would be 3 times 3 times 3

---

=27^(1/3) would take the cube root of 27

In that last example, by putting 1/3 in parens I told Excel to make that calculation first before take the root of 27.

If you’re going to combine calculation steps within one cell, you need to be careful that you properly place your parens so that calculations are performed in the correct order.

There is a help document on this titled Calculation operators and precedence in Excel which you can find through the Help tab by searching for order of precedence.

That help document lists the order in which calculations are done by Excel and also lists a number of operators (such as > for greater than) that are useful to know when working with formulas and functions in Excel. We’ll cover those later as they come up in relation to certain functions like the IF function, but they’re basically the same as you ran into in math class except you write >= and <= in Excel because there is no combined symbol for greater than or equal to or for less than or equal to.

If you’re building a really complex formula it’s always a good idea to test it as you go to make sure that all of the components are working properly and that the end result is the expected result. So I will build each component separately before combining them all in one cell.

But do check to see if there’s a function that already does what you want. Especially when dealing with common calculations, there just might be. For example, there is a function for calculating net present value.

Also, before we move on to functions, remember the saying garbage in, garbage out. Excel is not a person. It can’t read your mind and know what you meant, all it can do is take what you give it and return the result. So always, always gut check any result you get from Excel. Does that actually make sense?

I will often double-check a complex formula by creating the same calculation two different ways to see if the result is the same.

For example, with the net present value function, when first working with it, I’ll also do that calculation old school in Excel using basic math like we just discussed to see if I get the same result both ways. Once I’m comfortable that I’m giving Excel the right inputs, then I can stop doing that.

Also, if you give Excel the wrong kind of inputs or fail to give Excel the inputs it needs, you will get an error message. We discuss the types of error messages at the end, but in the meantime if you do get an error message I’d suggest that you check that the data in your formula is formatted as the right data type, confirm that you have matching opening and closing parens, and verify (for a function) that you provided all of the required inputs. That’s usually where things go wrong.

Alright. Next we’ll talk about where to find functions in Excel.

WHERE TO FIND FUNCTIONS

In this book we’re going to cover approximately one hundred Excel functions, sixty in detail, that I thought were most useful for the largest number of people. But there are far, far more functions than that in Excel, and chances are at some point you’ll need one I didn’t cover here.

To find the functions available in Excel, you can go to the Formulas tab. There is a section called Function Library that lists various categories of functions. Mine shows Recently Used, Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, and then there’s a dropdown for More Functions that shows the categories Statistical, Engineering, Cube, Information, Compatibility, and Web.

Function library options

Click on the dropdown arrow next to any of the categories and you’ll see a listing of functions that fall under that heading.

Now, unless you know what you’re looking for, this listing probably won’t help you much because the functions are named things like ACCRINT and IFNA.

You can hold your cursor over each of the names and Excel will provide a brief description of the function for you, but for some of the lists that’s a lot of functions to look through.

Function description in function library dropdown

Each description also includes a Tell Me More at the end of the description. If you click on that option, the Excel Help task pane will appear. You can then click on the category for the function (in this case Logical) and then choose the function from the list you see there.

Function help

This will bring up an additional set of information specific to that function that will generally include the definition for that function as well as examples and further discussion of how the function works and any limitations it might have. The complexity of the help varies by function.

AND Function help example

That will give you information on a function, but I do it differently because I’m usually looking to use a function.

Instead, what I do is click into my cell and then use the Insert Function option available on the far left-hand side of the Formulas tab.

Clicking on Insert Function will bring up the Insert Function dialogue box.

Insert Function dialogue box

In the top section under where it says Search for a function you can type what you’re looking to do and then click on Go. (Be sure that the category dropdown right below the search box is set to All unless you know for sure