Understanding the hierarchy of operations in Excel formulas

- When you work with certain formulas in Excel you need to be concerned with what's called, "the hierarchy of operations." Now, you probably won't use that phrase and you won't see it in too many Excel books. But it is an important concept. In this worksheet called, "Hierarchy," let's say that we're about to write a formula in cell C7. The percent of sales change between January and February. If you're familiar with the concept, you know that we have to figure out the difference first, that's going to be 60. And then we'll divide by our starting number, which is 120.

And the answer's going to be 0.5, or 50 percent. We've grown by 50 percent if we're measuring those two numbers. And we wanna get to that answer by way of a formula here in cell C7. So, equal, C4 minus B4. We know that's going to be 60. And then we will divide this by the 120 and then get our 0.5 or 50 percent. But as we press Return, that's not what's happening at all. So what has Excel done here? I'm going to double-click on the cell.

Excel has does the division first. B4 divided by B4 is one. Remember, C4 contains 180. 180 minus one, 179. Let's revisit this again, double-click. What Excel really does on a formula is it looks at the operators. Here's a minus, here's a division symbol. What happens first? If you look at the list in column A, in cells A11 through A14, top-down, in effect that's how Excel calculates formulas.

It looks for the symbols that it sees in the formula and looks down the list. The slash, meaning division, the asterisk for multiplication, occur above, or they're more important than, addition and subtraction, which is below it. So Excel performs this operation first. But what's at the top of this list? Parentheses. If we put a left paren just to the left of the C4 and a right paren to the right of the first B4, we are now saying, "Do what's inside the parentheses first, "then do the division." And here when we press Return, we will get the answer that we had expected.

If you'd like to format that as a percent, you can certainly click the Percent button. It's within the number group on the Home tab of the ribbon. You could also press Control-Shift-%. And then we copy that across. Let's get our other percent of sales increases and also drag this down from the corner. Let's go back to this original formula. Sometimes when you put in parentheses, you forget one or you put it in the wrong place. I'm going to leave off the initial left paren, let's imagine I forgot this. And now as I press Return, I get not an answer, but a suggestion from Excel as to what I might have done wrong.

Doesn't quite tell me exactly what to do. So I'll click OK and come back here now. Maybe I figured this out, maybe I haven't, I'll put in the left parenthesis here and maybe I'll leave off the one behind the B4. And then press Return. This time, Excel comes up with a suggestion, but is it a correct one? Looks like it isn't really. We'll click Yes just to see. And you see what's happening there. So, at different times Excel might give you the correct suggestion, other times not. But any time you are using parentheses or a series of them, for every left paren there must be a right paren.

What some people do, and it's not really wrong, but they might put in extra parentheses. For example, here. The parentheses around the second B4 is not really required, but it's not going to hurt. And we see the answer there, it is correct. And I've even seen an extra paren put in here just behind the equal sign, another one over there, and another one on the right. So you might say it's overkill. But be sensitive to the idea that when you do have a wrong answer, and some times the wrong answer just jumps out at you, first thing to look for is, did I not use parentheses where I should have? Or maybe I used them in the wrong place.

We're worried about these characters over here, many people will not ever use the caret symbol. It's used for raising a number to a power, it's like using the superscript in math. So if I need to get the square of the 20 up here, equal, that cell, squared, caret symbol above the number six key and then a two. If I need it to be cubed, of course it would be a three. If you ever need to do a cube root, a fourth root, a fifth root, and many of you will not need that, but if you did, equal, for example, this cell here.

I need to get the cube root of 20, that's the caret symbol and this time followed by, in parentheses, 1/3. Now that may or may not be bringing back fond memories of high school or college math, but that's how we do a cube root in Excel. So, more important though is this idea, the hierarchy of operations. Excel does not perform calculations left-to-right. It performs them on the basis of the symbols that it sees. And this formula here, even though it has extra parentheses, it will perform this operation first, then the division, and give us the answer that we want.