Calculate % of change

- [Instructor] Calculating percent of change is a relatively straight-forward formula in excel. But when we're trying to average a percentage change over a period of months or years, it gets a little bit trickier. In this worksheet, we've got some sales numbers in row two. In data row six, we're calculating the sales change month to month. Here's the formula right here in cell C6. We subtract the two entries, the two timeframes in question. And then we divide by the starting entry. And that's what we're doing in all these here too. By the way, slightly shorter version of this with the same answer would be equal the later entry divided by the earlier entry minus one.

Algebraically that's the same. That's why you'd be getting the same answers. So we could be using those. Momentarily here I'll press control tilde so that we can see those formulas better. Very similar, it gives us the same answer. And whatever's more convenient to you or what makes sense to you, use that one. Control tilde again takes us back to the standard display. So if we wanted to know what was the percent of change overall from 2012 to 2017, we could simply write a formula equal that 2017 entry divided by the entry for 2012.

So that's 7200 divided by 3900 minus one. Same kind of calculation. Using either one of these variations. 84.6, that's the amount of change. That's how much we grew over the five different change periods. Now the other question that comes up though, what is this average change here? If you highlight these, you'll see at the bottom of the screen 14.6. I'm going to actually put that in right here, equal average. Like this. This is not correct but it's reasonably close and you could easily be fooled into thinking that's the way it's done.

14.6 percent. But the way we do this by way of a formula needs to involve exponentiation. There are five different change periods here. We need to take the fifth root of that difference. So our formula's going to be equal and in parenthesis, G2 divided by B2, right parenthesis, and we're going to raise this, the exponentiation symbol above the number six key to the one-fifth power because there were five different change periods.

And the answer is here it the true answer. That's 113 percent. Now, in certain sense, that's correct. We could live with it. It's easier to work with and it's easier to state what we've done by subtracting one. This 113 percent means the following. If we were to start with 3900 dollars and show what the 113 percent increase is from 2013, then 2014 each time multiplying the newly derived answer by 113 percent, by the end, we have 7200 dollars.

But it's easier to work with than to state, by subtracting one here, in other words, we grew 13 percent per month on average. By the way, that might not be totally exact. Up in the formula bar, let's increase the decimal. You can see we don't necessarily have to go too far with that, but that's the exact entry. Now, let's test this out in reverse using not a formula, but the feature called goal seek. And we could be using this in a variety of ways. First of all, let's just work with the current numbers. I'm going to put in 3900 here.

Let's test this out. If we grew at 13.046 percent a month, would we really reach 7200? Well, let's temporarily here put that number in and put a guess number out here. How about 15 percent? Let's say we're guessing that, It's going to be something like that. The guess can be wildly off mark too. So here's a formula. Equal this number here times this amount. We want that to be an absolute address. We will be copying to the right. I'll press the function key F4. Plus this amount.

Now this isn't really what happened in 2013, but this is what would happen if we had grown at 13 percent. Well we're going to copy this formula. The one that takes the previous entry and increases it by 15 percent. We're going to drag this across into 2017. It's going to be too high 'cause we know ahead of time here. At least what the answer is going to be. It's that. Now with goal seek though, this is where we will come up with an exact answer and it will correct and give us the correct entry for cell A9. So this formula right here is highlighted.

We go to the data tab. What if analysis. Goal seek. Find the right input for the value you want. Now if we click this. I sometimes jokingly describe this as I don't like this answer I want to different answer. We want this value in G9 to be exactly 7200 dollars. By changing which cell? The 15 percent over in cell A9. Click okay. We have an answer that's not formatted. If we want to keep it, we click okay. If not, we cancel. Let's click okay.

Jump over to A9, increase the decimals. This is from the home tab up in the ribbon. We'll show more decimals. So we've used goal seek here to verify that that truly is the answer. This is the average percent of growth per month if we grow from 3900 to 7200. We could certainly work with this and move ahead possibly. How about 2017 numbers, let's look ahead. Using an exponentiation formula or goal seek to tell us what we will be, so to speak. In the year 2024, if we grow at a certain percent per year.

So using either goal seek or an unusual kind of exponentiation formula, we can work with these average percentages. Two tools for helping us calculate answers in more complex scenarios like we've seen in this worksheet.