Join Dennis Taylor for an in-depth discussion in this video Reviewing function basics, part of Excel 2010: Advanced Formulas and Functions.
The latest version of Excel, Excel 2010, has more than 400 functions. The list has been expanded. And anytime you need to use a function, if you're not clear as to what's available and the many functions that are available in Excel, that is, you need to go to the Formulas tab. Now this is one of a number of different ways to get a hand on what's available, in terms of functions. On the Formulas tab, in the Function Library, you'll see major categories of functions. Just picking any one of these, for example, say Date & Time.
In Excel 2010, you will begin to recognize the fact that there are some new types of functions. I won't go into these in detail right now, but here is one: Networkdays.intl; that's a new function. Any of these that have dots in them are likely to be new, and it's an expansion of the capability of certain kinds of functions. Now when you're trying to achieve a certain objective and the formulas that you're using just don't seem to work, or you can't figure out how to make them work, there are times when what you're trying to do is already available as a function.
One example could be you're looking at the salaries here, and you're familiar with some basic statistics, you certainly you have figured out how to do a sum, an average, perhaps a median, you want to a standard deviation. Now the technique for doing that isn't that involved, but it does involve averaging these and then taking the average difference between the each one in the average and adding them up and taking the square root. I forget the details. You can certainly put together a formula to do that manually.
But of course, many people know that there is a function called StandardDeviation. So the question might be, how do you find it? I'm still little surprised at the list here that we don't see, at list initially, statistical. But it is there, under More Functions, and that would certainly be a good place to look for a function called StandardDeviation. Now, it's probably not going to be spelled that, but a good guess would be it's going to begin with S. And it's just a reminder as to how we find things in Excel. This is certainly one way.
And as it turns out, we see a number of functions that begin with STDEV, and sure enough here is one standard deviation based on an entire population. You wouldn't be looking here unless you have some sense of what this function does, or some knowledge of standard deviation, but it's up to you to decide which one is the one you want to use. These pop-ups, these descriptions here, are rather extensive, and they tell us a lot about potential functions that we might be using. So that's certainly available.
And don't overlook another possibility for checking out functions: by clicking the fx button in the formula bar, you ultimately can get to the same place that we just got to do, but perhaps in a different way. The approach here might be maybe you start by looking at All functions. Now, who knows where the functions going to be? A good guess with standard deviation, of course, is it would begin with S, even if you didn't see the previous search for it. But it's likely to be in here, and sure enough, you'll find it here as well.
And what happens here when we click one of these, we see a description below it, not unlike what we saw earlier. So we certainly see some of these here. Another approach could be I'm not sure what kind of a function it is, but maybe-- yeah, I will bet it's Statistical. We could find it there perhaps more readily because the list is smaller. And a third way, if you didn't guess what it began with, maybe type a brief description. And if you type in "standard deviation"--that's certainly the thing that would make sense here--and click Go, you're going to get an abbreviated list here.
Now that may not be as short as you want. As it turns out, that's lengthier than that I would want it to be. But sure enough, here and there with certain kinds of functions, you will get a short list. And at different times, I've found this to be very helpful. If, for example, you were looking for a function that would calculate the monthly payment on a loan, you might just type in "loan payment". That might or might not be enough to narrow it very much. On the other hand, it looks like this list is certainly shorter than the one we saw. This is 15 or 20. But by changing the wording here, maybe that will help.
Many times what you do next is click here to get the brief description. So you want to have the sense that you can find functions, even if there is no one around to ask, or if you don't have a book that lists all of these. And when you do find a function, too, don't overlook Help on this function-- extremely valuable at times. You can either get online or off- line help and many, many times these screens are valuable. You'll notice also at the top you can print these. So in a certain sense, we're talking about the basics of how to find functions, find out what they mean, see some of these, print out the help screen potentially, or maybe even say, you know, that's pretty close to what I want. Why don't we right-click, press Ctrl+C and then paste that into a worksheet? You can certainly do that as well.
So there are quite a few approaches to getting that cliched, "get a handle on something," understand what kinds of functions are available. And certainly here and there, you'll just make good guess, too. I mean we don't necessarily have to be told. We would probably make a good guess. Just a little bit of knowledge would suggest there is got to be a function called median. Maybe I'll just type it in. And sure enough, like a lot of functions, that's going to work just fine. Let's highlight this data. I want to know the median value here. Good enough. So whether you're typing in a function name and making a guess or using fx or using the Formulas tab, you'd likely define the function eventually.
Another thing I want to point out here too: if a function I'm about to try begins with a letter S--or at least I think it does-- I'm going to type =. And regardless of what the letter is, as soon as I press the key for the first letter, I get a complete list of all functions that begin with that letter. And here it also, in occasions like, type time saver too, I don't really want to use Standardize. But if I do, I'm not going to be typing it. I'll just click this and press Tab, and it pops it into place that way. And then of course, as with any function, once we have the function name and parentheses, we do see the pop-up tip here.
And I would say that when you're using functions that you have almost no familiarity with, or maybe you haven't seen in a long, long time, it certainly is going to be helpful, for example, to either press fx, and read about the function possibly. Or, by pressing OK, Excel will lead you through the steps of putting in the arguments in the appropriate places. So take advantage of that. It's certainly going to help for those functions that you use only occasionally, or maybe you've haven't seen this in a long, long time.
It gives you that kick-start to remember. So there is any number of different approaches in Excel to get into functions.
- Referencing, copying, updating, and converting formulas
- Using the logical functions and creating compound logic tests
- Searching for and matching data based on specific criteria
- Reconfiguring cell data using text functions
- Calculating dates, times, and days of the week
- Analyzing mathematical and financial data
- Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
- Working with rounding functions
- Returning cell references