Microsoft Excel 2013 Basics: Creating Simple Formulas

show more Using other common functions provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Using other common functions

With over 400 functions in Excel, you may be wondering "how do you get a handle on them, how do you know what to look for? We're going to give you a few examples on this functions worksheet, which is found in the Workbook 03- Creating Formulas and Functions. When you do have that extra moment, if you go to the Formulas tab in the ribbon, recognize that there are some groupings of functions here. Now, depending upon what it is you're doing, maybe you'll click on Math & Trig and begin to see some of the many functions here. Most of us don't have the time to do this, maybe not even the inclination, but if you do pause and hover over one of these, you do get a description of them.

Sure enough, some of these names are going to jump out at you, something about degrees maybe. Converts radiance to degrees that may or may not be useful to you. Here's something called Fact, which opens us to factorials which is something you may have heard of. Logarithms-- those are built-in there. It sounds like maybe we're in some big fishing expedition maybe, but, based on the work that you do, maybe you do work with financial functions or financial data, and here's a financial group over here. Sure enough, some of these are likely to have relevance to what you do.

Here's a nice one here for calculating mortgage payments, that sort of thing. There are tons of functions. You just don't always know where to begin. Date & Time functions can be pretty interesting for a lot of Excel users as well. There's one in here for picking out the day of the week called Weekday. There's another one for calculating monthly differences, differences between months or so many months out. There's an EDATE reference right there, so many months before or after; there's an EOMONTH. This just goes on and on and on. Let's talk about a couple of functions that you might not have heard about, you wouldn't necessarily need, but on the other hand, I think they have a lot of relevance to certain kinds of lists.

Here's a simple one here. We simply want to count how many names are in column A. It might be a huge list, it might not be. We can count the number of cells that have data in them. That function is called Count A. Now, anytime you type equal and then a letter, you'd see all the functions that begin with that name. Now, I could certainly type this faster than finding it, but nevertheless, if you do find it, for example, "Count"--we see it in there--Count A right here, this particular function counts the number of cells on a range that are not empty.

Rather than typing it, we can press Tab right now and use that. Where are we looking? Maybe we're looking in Column A. By the way, it will ignore the icon, the image that's in cell A1, sitting on top of cell A1. People ignore that. If we want to know how many cells have data here, we are counting cell A2 as well. If the question is, "do we want to count how many people work here?" Well, we'd want to subtract 1 from this, but if it's simply a count of how many cells have data, there is our answer, it's 13. If we wanted to change that wording to the right, let's say number of people, then we'd subtract 1 here.

If we want to count the cells that have numbers, how many different salaries do we have here, it's almost the same, but it's called Count. Here, we'll just click Column E. By the way, referring to an entire column often makes sense. If there's nothing else in this column--now there is text in cell E2, but there's nothing else there--and we're trying to count the number cells--we should get sensible answer here-- there are 12 of them. In this list here, we're dragging downward to see that. By the way, when you drag downward, you will see in the upper left corner to the left of the formula bar, the indicator up there, and you don't see it at the moment--but as I highlight this again, keep an eye on this area--the name box, to the left of the formula bar here, will indicate how many cells I'm highlighting, and it does it in the following style that says, "12Rx1C"--meaning 12 rows by one column.

There are 12 cells here that have numbers in them and here's the formula again. Using the Count function, count the number of cells in Column E that have numbers in them. Now, Median is a commonly used number when dealing with certain kinds of entries. Earlier, you might have seen how on the Formulas tab, you can use the drop arrow for AutoSum to get to a Max or a Min, but we don't see Median in here, so it might be faster in this case to actually type in the name of that function. How do you know there's a Median function? You happened to discover it.

It's probably a good guess, because that's a commonly used statistical measure. The Median salary, say for the New Salaries in column F, What's the Median Salary? There it is right there. Is it different than the average? Well, we could calculate the average, just as easily, and let me move this down actually. Let's do an average as well right here. This time we'll use the AutoSum drop arrow, choose Average, and we don't want that average, but we want the average of column F, and Enter. Of course it will make sense to format both of these and compare them.

Go to the Home Tab. Click the comma button, good enough for now, the Median and also the Average. Now, finding this 2nd Largest entry, it may or may not be that important--it wasn't to me until a few years ago when I needed it a couple of times--and I thought you'd do something like a "max" or "sort the data", but there is a function way, and it's called Large. I don't think you'd exactly guess that. Large says where are we looking? We're looking in column F, comma. The 2nd highest, put in the two, third highest, put in the three, and so on.

What's the second highest salary there? 76,728. It doesn't tell us where it is, but it tells us that it exist in here. Here's an 81,000, so that's the highest. It looks like somewhere in there, we've got a 76,000, there it is right there, that's the second highest. Now, if you're familiar with statistics, you would know about standard deviation. Now, do you make a guess here? Do you start typing? Maybe. Perhaps better-- go to the Formulas Tab. Strange enough, you don't see statistical here at first, but if you go to More functions, you do see statistical and quite a few entries here.

You could probably guess--and guess correctly--if it's going to be standard deviation it begins with the letter "S", probably ST. As it turns out, there's a cluster of them here, and you just have to figure out which one suits your needs best. For those of you who have used this, you know--you have some sense of what it does-- it indicates how much variance there is between the average and each of the entries here. It does it in a waited kind of way. Maybe it's this one, maybe it's this one. You'll know which one to use. That example here, it actually will prompt you into highlighting the data, for example, column F, and we'll click OK and get our answer.

Now, if you're familiar with standard deviation and how that works, that's a meaningful value. It's hard to come up with a master list of the functions that you need or that I need or the next person needs, because so many different people use Excel in so many different ways; but the Formulas Tab, at least opens you up to some of the major categories, gives you some ideas and as you work with these more and more, you'll have a better sense of which of these functions is likely to be used. There's just a ton of them. Don't get overwhelmed, but there's no question, they're going to save you a great deal of time as you work with Excel.

Using other common functions
Video duration: 7m 0s 6h 32m Appropriate for all


Using other common functions provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...