From the course: Excel 2010 Essential Training

Understanding formulas and functions - Microsoft Excel Tutorial

From the course: Excel 2010 Essential Training

Start my 1-month free trial

Understanding formulas and functions

“

Let's talk a little bit about calculations. Excel does calculations using formulas and using functions. Yes, I know the term sounds similar. So let's make this a little bit more clear. Formulas and functions are the same in that they always start with an equal sign. There's never any exception. When you're calculating in pencil and paper, well the equals sign comes in the middle, doesn't it? Well, that's not the way it is with Excel. With Excel, you always start it at the beginning. So let's talk a little bit about formulas. Formulas are basic arithmetic: addition, subtraction, multiplication, division, and exponents. And if you're not familiar, an exponent is simply raising something to a power. Like if you're taking a number and squaring it or cubing it. Well, let's take a look at what's happening in Excel. In this workbook, we have a very simple formula. And I think it'll make sense to you if we do this kind of the wrong way first, and then the right way. In the first worksheet here, the Formulas worksheet, we have got a couple of numbers, right? There is one number. There is another number, and two ways of calculating it. This way I have it hard-coded and this way I have it using a formula. Now, the formula here, I'm simply saying = 2 + 2. But we don't really want to do that. We really want to say not what is 2+2, but what is the total of this cell, D4, where Column D meets Row 4, plus the content of this cell, D5. So the wrong way is to say =2+2. The right way is to say equals the contents of D4 plus the content of D5. You might wonder well, what's the big difference? They're both fine, they both have the correct number. But what if we do this? Let's take that number, and let's just change that. I'll just change it to a 3 and Enter. Well, now this is wrong, because it's still 2+2. So this formula has absolutely nothing to do with what's happening over there, because it's 2+2. It's not the content of D4 and D5. This one has a correct number because this formula is D4+D5. Let's talk about functions here. There are over 300 functions and don't worry, we are not going to go through every single one. But you should know that there are functions for so many different tasks. Finance, statistics, engineering, physics, and so on. So functions are like pre-made named formulas. Every one has its own specific task, and they all use at least one set of parentheses. Let's take a look at the syntax. The general syntax of a function is you have the equal sign, we know that. Then you have the name of the function and then you have at least one set of parentheses. Now, in the parenthesis, some functions will have a lot inside the parenthesis. There are some functions that had nothing inside the parenthesis, but you have to have at least one. Well, here are some sample functions. An Average function, if you want to take the average of some numbers. You might want to take the square root of some numbers. Or you might want to figure out what's the payment if you are going to borrow some money and we have an interest rate, and payment periods and all that jazz. So you could find out how much you're going to have to pay every month. We are going to use that also later in the course. So let's take a look at an example of using the function. In this workbook here, go down and click the functions worksheet. Now, assume we have a column of numbers and we want to get the average. Now, the wrong way to do it is to do it manually. And we could add up all of these numbers here in Column D and then divide by how many numbers there are. Well, that's really not a great way of doing it, because first thing we have to figure out is how many numbers are. I can click on the first one and see well it's Row 2, click on the last one see that's Row 15. You could figure out there are 14 numbers, and that's not a very good way of doing it. You could select all of the numbers, and then down here in the status bar, this will show you that you have 14 selected. Still too prone for errors. But the thing is, you're looking at it and say well, wait a minute doing it the manual way, and I have this using the Average function, they are both correct. So what's the problem? Well, what happens if you change something? Let's take this number. Maybe if I just change a number, well the numbers are still correct. Now, I'll take this number here, and I'll change it, and the numbers are still the same. But what happens if we delete a few? Now, all the sudden there aren't 14 numbers. There are 12 numbers. Well, this one is still divided by 14, so that's a wrong number. This one is using the Average function. And this is saying hey just take the average of this bunch of numbers. So that's why this is giving you the correct answer. So now that we see what formulas and functions are all about, let's go and do something useful with them.

Contents