Welcome to Highline Excel 2013 class video number three.
In this video we wanna talk about how Excel calculates formulas.
Let's just look at an example.
We have beginning quantity, ending quantity and a value and we need to figure out Cost of Goods Sold.
First we need to calculate how many we actually sold.
So there's seven, so seven times five is 35 bucks, that's gonna be the cost of the items we sold.
Here is a formula.
This is actually a question I got, and the person asked this question.
"Hey, I made a formal, and this is the formula, and Excel is calculating incorrectly.
Can you please tell me how to get Excel to calculate this correctly?"
Well, the problem was not with Excel because Excel knows math order of operations
and so what's happening here is multiplication is done before subtraction,
so if you don't know your math order of operations then getting Excel formulas to work can be quite difficult.
What it did here is five times three is 15, so 10 minus 15 is -5 and that's how you get -5.
So I have of a question for you.
We'll see how to fix that in just a moment.
What's the answer to eight plus two times two?
Is it 20 or 12?
Well when in doubt, go ahead and have Excel do it.
So, equals eight plus two times two.
Its twelve and the reason why is because the multiplication is done before the addition.
If you wanna see Excel in action, there's a great feature.
You go over to the Formula tabs and Evaluate Formula.
This great feature, if you click evaluate or enter, notice that the button is blue, that means if you hit enter it will enact that.
If you look up here, that is underlined, that means it is going to do multiplication first,
so it calculate four and then eight plus four is 12.
and that evaluate formula will come in handy when we do some more complicated formulas and we have an error, and we're like
"Hey, which part of the formula is calculating the problem?"
Hey, if you really want to force addition first, then you have to use parentheses,
so eight plus two, the parentheses force the addition to be done first and then times two.
So what's happening here is eight plus two is 10, times two is 20.
So, back up to formulas and evalute formula.
Now this is something I do a lot,
So I know the keyboard.
Alt and you see that Formula pops up an "M" so you hit "M" and then V for evaluate.
Alt + M + V
and there it is.
So, now you can see the underline is under the subtraction first, because it's in parentheses.
I'm gonna hit Enter, it evaluates that, and then it says10, and actually has to get rid of those parentheses, and now it says 10 times 2 is 20.
Evaluate Formula, a great trick.
So here is the order of operations for math, we gotta do our parentheses, our exponents, multiply and divide left to right,
and then add and subtract left to right.
If you don't know this then getting your formulas to work can be quite difficult.
We don't want to do this for calculating inventory value.
We want to go equals, open parenthesis,
hey we're going to take our begin quantity minus our end quantity,
end parentheses and multiply it by the value for each, and boom, our cost of goods sold is properly 35 bucks.
Hey let's go over to the sheet "How Formulas Calculate".
This is very helpful to know your math order of operations but Excel has a bunch of other things
it has to consider too, so we're going to go over here to How Formulas Calculate, here is the list of how it calculates.
It starts with parentheses first and it goes to reference operators, Colon, Space, and Comma.
Now Colon we've seen before.
We know that if we give it a range of cells, it has to evaluate that and get all the numbers and throw it back into the SUM.
The intersection operator, I will show you an example of that but it is incredibly obscure, almost never used.
Comma, it's called a union.
That's very important because you actually add different ranges or cell references
into the sum function by just adding new arguments, and adding commas.
Negation comes third, then converting percents,
if you ever type a percent into the formula that is,
I don't think I've ever done that, for the robust efficient formula ever.
I suppose it's possible though, but if you type this in your formula it actually calculates it, to get to the number.
Then we have exponents, then multiplication, division left to right,
adding and subtract left to right, ampersand, and the last thing that is evaluated when evaluating in a formula is comparative operators.
Now let's go look at some examples.
Alright, we're going to look at a few of the items from that list.
Now the colon is easy enough, we all use it, we may just have never thought about it this way.
Of course it has to evaluate that colon to see what numbers are in that range.
Now watch this, this is interesting.
If we use ALT + M + V
and use formula evaluator of Evaluate Formula, it just underlines it and evaluates it in one single step.
There's gonna be sometimes that the evaluate formula dialogue box will not show you all the steps.
I'm gonna click Escape there.
That's why our F9 key is so important.
Now it's a great little trick is if you have your screen tips showing, if you click on the argument,
it will highlight everything sitting in that argument.
It's easier than dragging and highlighting.
Now, if I hit F9, you can see, sure enough, that colon has to be evaluated to find,
oh, there's these four numbers, before the SUM function can add.
Now, you could CTRL + Z , however Escape will always get back to what ever was in the cell before you put it in edit mode.
So this formula was already in the cell, so when I hit Escape it goes back to that range.
An intersection, this is the strange thing that you will never see,
the only reason you want to learn this is so you can do it as a party trick.
Alright, watch this.
I'm going to highlight,
Ok, that's a row, and a Space operator, and then highlight another range, in this case a column,
if there's an intersecting value, and you can see right there, its 25,
that space operator says "Hey, go get that."
Now if you happen to do, and I don't have a cell for this, but I'll do it just to show you,
Space, and there's no intersection, then you'll get #NULL! error.
That NULL error means there's no intersection there.
I'm gonna leave that there.
Alright now Comma,
again if you ever have to do a two way lookup of an intersection of a column and a row,
we'll see how to do that with lookup formulas that are more efficient in the long run than the intersecting space operator.
But that's good for a party trick. You could bet someone "I betcha I can get the space to do an intersection."
Comma, we've seen this, perhaps, but let's just imagine we want to January, but not February and March.
Well, we can just Comma and go and highlight as many ranges as I want in the SUM function.
Now this is the SUM, but you could be doing Average, Max, Min,
any what are called aggregate functions, that look at a bunch of numbers and calculate a single answer.
This Comma, Comma, Comma
It's a union here, it means is gonna go get all of of the numbers before it adds and then delivers an answer to the cell.
So the Colon is first and then the union, Comma, operator.
Again these are not things you really need to think about too much 'cause that seems automatic.
Now, negation, and this is like on negative so if I say equals -2 and then use the carrot ^ that means raise to the exponent,
two raised to the second power, or two squared is four, but guess what that minus sign, if you really wanted to say take the opposite,
that's what negation means, take the opposite of this calculation, this isn't gonna work because
then negation operator there will operate on the two before the exponent,
so what's -2 times -2?
A negative times a negative is a positive.
So if you really wanted to have a negation of that operation, again you'd have to do it in parenthesis like this.
Force the exponent to happen first and then the negation.
Percentage.
I would never do a formula like this but 10 times one percent.
I don't think ever in my whole Excel life have I ever typed a percentage into a formula.
Maybe I'm just not doing the right formulas.
Now I'm gonna enter this one
And actually, let's---
we'll run all three of these with formula evaluators so you can see.
I'm going to run this one right now, ALT + M + V, and you can see up there, it's underlining the percent, so when I hit enter,
Oh, it converts it to a number and now it can calculate 0.1
Escape,
I'm going to click in this cell, ALT + M + V, and run the formula evaluator, notice it's doing the exponent inside the parentheses
and gets a parentheses and then it takes the opposite of that.
ALT + M + V
Here it's just gonna do the whole operation 'cause that -2 is sitting there, -2 times -2 raised to the second power gives us four.
Alright, multiplication, I'm just gonna make something up here so we can watch it when we run formula evaluator.
Equals six times two plus 10 divided by two minus seven.
Now it'll have to do the multiplication and then the division first
and then go back and start over left to right and then do the adding and the subtracting,
so if I CTRL + Enter, now let's run Formula Evaluator, ALT+M+V, and let's watch it in action.
Multiplication first, then division, then it goes back to the front, addition, subtract and we get our answer of ten.
The ampersand, now this is going to be a wild one.
Notice over here we have the start and end date for our project.
Well we learned last week, our formula for number of days,
so I'm going to say "End" minus "Start" but since this is a project and we want to include the first day, we have to add one.
So we get four.
That's a formula right there.
Notice it did the adding and subtracting left to right, but now I want this to be a label that says the project lasted four days.
So the text in a formula must be in double quotes, so I'm gonna say---
I misspelled the word there too, but I added a space there 'cause I want there to be a space between the math calculation in this text here,
and notice how everything is black, that means it's not a valid formula.
When I type the ampersand, the join symbol, the color comes back on, that means it's a valid formula,
that sometimes, for longer formulas is quite helpful,
to watch when the color goes away you know there's a problem somewhere in your formula.
Alright, now I want it to say the project lasted four days,
so I'm gonna come to end and ampersand, now remember I want to add a space, so I'm going to double quote, Space and then days,
Period, end double quote.
Now I'm going to spell check this while it's in edit mode, because once you enter it into the cells, then spell check on the surface,
the spreadsheet won't catch misspelled words inside of text inside a formula.
So I'm gonna hit F7, The Project Lasted, there we go.
And it says you're good to go.
That's new in 2013, it didn't used to be as casual like that, it's trying to keep up with Google and Apple.
Alright, CTRL+Enter.
Now the whole point of this is, let's watch.
What gets calculated first?
Is it gonna be math operators or the join symbol?
Well from our list above we know the join symbol comes in second to last,
so I'm gonna ALT+M+V,
and watch this.
Enter.
Oh it's doing the date, it's getting rid of the formatting, it's looking what's in that cell,
so it's got two serial numbers, it does the subtraction, and it does the next math, left to right, adding,
and now it goes back to the front and does the ampersand,
and then the ampersand again, and then we get our label or text formula.
I'm gonna click close.
Alright, how about the last one which is comparative operators?
So I have Max Days Allowed for this project, I'm gonna say equals,
Are the Max Days Allowed greater than or equal to, and now I'm gonna take my "End" minus my "Start" plus one.
So here we have math coming near the end and a comparative operator at the front of this equation.
According to the hierarchy of how Excel calculates a formula, or Excels order of precedence,
it better do all that math first.
ALT+M+V
Well it looks like it's going to look in the cell reference, then it's going to look in the cell reference,
then it's gonna look again in the cell reference, do the first adding or subtracting, so it does the subtracting and then adding
and then it goes back to the comparative operator.
So that was just a little example of some of what is in this list right here.
How formulas calculate.
Later in the class, when we get bigger formulas, we'll see that knowing this complete list
can help us shorten formulas in certain circumstances and that in other circumstances,
it'll just help us get the right formula in the first place.
Alright, next video we will look at some increase decrease problems.
Alright, see you next video,