Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this worksheet we might want to calculate how much our Profits have changed month by month. We can certainly do this with the Overhead numbers and also of course with the Sales too. Let's say we want to focus on Profits. Now, is it always going to be an increase? It so happen that this day it is going upward, but not always, so maybe that wording there is a little bit optimistic; perhaps a better heading here might be "%ProfitChange". We're not going to have any number whatsoever in the January column, but in February we will, because this involves comparing what's happened over a two-month period.
How much has the Profit percent gone up, we hope, or possibly gone down over these two months? It has gone up. How do we make this calculation? This may or may not be bringing back fond memories of high school math. You probably learned that back then, but do you use this capability all the time? Maybe not. It's relatively straightforward formula, but it does bring up a major issue with Excel formulas. Let's talk about how we do this. To calculate the %ProfitChange, we need to first figure out how much change has occurred.
We need to subtract these two and then divide by the starting point. In other words, the difference is 10, we'll divide it by 20, this represents 50% growth and that's what we hope to see in our Formula here. Let's do the subtraction first, equal the February entry minus the January entry, and then divide that by the January entry. We're expecting to see 50% or .5, something like that, and it comes as quite a shock when you see a number like this.
We're getting to the heart of the issue of what happens in Excel when you write a formula? What does Excel do first? If we're thinking standard calculators, well, we do the addition first, then the division, but in Excel that's not the case. Excel refers to what's called a "hierarchy of operations". What you see in column A, starting in row 8, is not something you're likely to see on your screen, but it does represent the hierarchy of actions. In performing this calculation, Excel, first of all looks down the list-- what's the first symbol in the list that's in the formula? Division, so this happens first, B4 divided by B4 is one, what's C4 equal to? That's 30, 30 minus 1, that's why we get the 29.
What we want to have happened first is the subtraction, so we put it in parentheses. And of all the mistakes you might make in writing Excel formulas, this is certainly one of the most common. You either forget to use parentheses or possibly you use them in the wrong order. By entering the formula this way, Excel in effect is saying, "All right, I see parentheses, I'll go there and do what's inside of them first, then I'll get to the division later". Now, we will get the subtraction done first, that's 10 divide by B4 which is 20, .5 is our answer, that's what we would expect.
On the Home Tab to make this even better, let's use the % button, it's found in the number group here to display 50%. This formula deals with the two cells up in row four in that order. We would expect this formula to deal with these two cells and this formula with these two cells and so on. As we drag this rightward in the column June, we get our answers. Keep in mind these are not always positive, they are in this case, so far, but if our April Overhead number gets adjusted to be 240, that's certainly not a positive number.
If your Profits go from 50 down to 10, they're down by 80%, so that's going to happen from time to time; but the main idea here is to recognize that when we are writing formulas--I almost want to say that whenever you have a mistake in a formula where you know the answer is dead wrong--first thing to look for is missing parentheses or misuse of parentheses. Excel operates on what's called the hierarchy of operations. Profit Change as it would be properly labeled here, is just an example of that issue and it's going to come up from time to time as you work with Excel formulas.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.