Excel formulas can include a variety of operators ( + * / parentheses ^ and more ), but if you use them incorrectly, you can create incorrect results, some of which are not always obvious. Learning the simple rules of hierarchy of operations in Excel makes formula writing simpler and more coherent.
- [Voiceover] We're looking at the file 01-Tools and the worksheet hierarchy. When you work with formulas in Excel, you do need to be concerned about the order of operations, sometimes called the hierarchy of operations. In cell C7, we're about to write a formula that's going to calculate the percent of sales change. Reviewing the two numbers up above, we grew from 120 to 180. We've grown by 60. The way we calculate this percent here, is to subtract these two numbers, we'll get a 60 there.
And then divide by the starting number. 60 divided by 120 is 0.5, and that of course means 50%. That's the answer we're looking for here. So we'll simply start with the formula: Equal C4, you can type it or click on it, minus B4 divided by B4. And again the numbers are simple enough to do these in your head. C4 minus B4 is 60. We're gonna divide that by 120, therefore 0.5.
But as we press return, we get a totally different answer. And the format of course makes it even worse, but how did we get a 179 here? I'm going to double click this cell to go into edit mode. As you can probably figure out pretty quickly, Excel has done the division portion of the formula first. B4 divided by B4 is one. C4, which has a 180 in it, C4 minus one equals 179. 180 minus one equals 179. That's why we get the answer.
Now, displayed on the screen over in column A, starting in row 12 is what we refer to sometimes as the hierarchy of operations. In fact, when Excel evaluates a formula it looks for the symbols as we see them over there in column A from top downward. So the first symbol that's in our formula that's also in that list, is division. Therefore, Excel performs this operation first. And any time there's a problem with a formula in Excel, one of the first things you want to look for is parenthesis.
Did you forget to use them or did you use them in the wrong way? If we simply put the subtraction portion of the formula within parenthesis like this, then Excel will perform that operation first. And now as we press return, we will get a sensible answer. 0.5, now obviously the format needs to be changed. The easiest way probably for many people, on the home tab there's that percent button right there. There it is. And we can copy that formula across by pointing to that fill hand on the lower right hand corner, dragging across into June.
And that shows our percent of increase month to month. So here for example in May, what are we doing? We're comparing these two cells. I think you can see the difference there is 60. We're dividing 60 by 240, therefore it's 0.25 percent. Now, notice as we look at these, the asterisk meaning multiplication, slash meaning division, are on the same level. When those are encountered in the same formula it's simply a question of what's occurring in the formula left to right. So they are of equal precedence you might say. Same thing with these two, plus and minus, when we see them together it's simply left to right as we see them in the formula.
But with parenthesis, and I'll go back to our original formula here in C7, it's not uncommon to see parenthesis being used more than is necessary. Usually not a problem, but it depends upon how you use them. It's not uncommon to see for example, this portion of the formula in parenthesis as well. That certainly doesn't hurt. It's overkill. It's not necessary, we'll get the same answer. I've even seen the entire formula put in parenthesis like this. That certainly is not necessary either, but not wrong. But, it's pretty apparent sometimes that you put in parenthesis, or you could put in parenthesis, incorrectly.
I'm gonna take off some of these here. And for example, leave them in like this, press return, I'm gonna get a warning here. Many times Excel will present us with an option, typically it's correct although not always. Click Okay, we do have a correct answer there. But there will be times, for example here, I'm going to leave a parenthesis at the beginning, but maybe I forgot the one at the end. Now Excel will mistakenly misinterpret this one as I press return. That really is not going to help the formula. If I click yes here and go with this, you'll see what's about to happen.
I think it's pretty apparent how we need to use these and how we don't need to use them. But if there's any doubt, any time when you see a formula that's giving you what you know is a wrong answer, first thing to look for is those parenthesis. You probably either forgot them or put them in the wrong place. And by the way, that symbol you see over in cell A13, the caret symbol, not too many people need that, but from time to time, depending on the kind of formula you might be writing, this is how we raise a number to a power. Now if we wanted to square the number for example, the profit number there in cell B6, we could type equal, click on B6, caret.
Now as a reminder as to what that means, if we were writing this for example squared, we write a little two as a superscript above this, so think of the arrow as pointing upward perhaps. So, 20 squared, we're going to see the number 400 here. And occasionally from time to time some of you might need to get roots of numbers. If we wanted the cube root of 20 for example, equal this number, raised to what power? Left parenthesis, one third. That's the one third root, or the cube root.
And of course it can be four for fourth root, five for fifth root, and so on and so on. So that's how you raise numbers to a fractional power. Cube root of 20 is 2.7. So, recapping again, as you work with formulas in Excel, most of the time you'll master quickly the idea of parenthesis, and any time you've got a formula that you know is obviously incorrect, first thing to look for, revisit the formula. Remember you can edit by double clicking in a cell. Review those parenthesis or lack thereof, and often that will fix the problem that causes the formula not to give you correct answers.
- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets