Let's talk about how Excel runs calculations and in which order it does it.
It's really important to understand this if you want to make sure you get the right numbers.
So let's do a quick test.
What do you think
The answer of this is? It's 12.
What about this one? It's 10.
2+1 is calculated first.
The answer is multiplied by 3 and that answer is added to 1. Understanding the order in which
calculations happen is key here.
Let's go through them.
The number one type of calculation that's run is any values that are in parenthesis or use reference operators.
By reference operators,
I mean the colon and the comma for example what we're used to seeing in the sum function.
Everything there occurs first and is calculated first before any other calculations occur.
Number two is negation. Then comes percentage, and then exponentiation.
After that we get multiplication and division.
So notice here in this example, we don't multiply first but we do two to the power of two first and then
the result is multiplied by two.
In the case of multiplication and division, if both exist in the formula
Excel evaluates from left to right. After this
we get addition and subtraction.
So if we have a formula like this, the first thing that's going to calculate is the 3+1 because
that's in brackets.
After that the multiplication is going to happen.
And after that the addition is going to happen.
So just remember it's MBA: multiplication before addition.
Number seven is comparison operators.
So for example, if we have 1+2 equals 1+2 it's not just going to check the 2 with
the 1. It's first going to run the addition because that occurs before comparing and then it's going
to run the comparison.
So, in this case the result would be true.
Let's jump to Excel and do some examples.
I'm in the start file for Section 5 in the first tab here you can test your knowledge before you see
the results.
So actually what I suggest you do is to pause this video and try to come up with the right answers and
then come back to the video and let's cross-check your answers.
Just one note here where I have SUM(C3:C4)
these are actually the numbers that are used in these formulas here.
OK so have a go at it.
Press pause and then come back.
Now we're back.
How did you do?
How many did you get right?
Let's take a closer look at these ones.
These are using our comparison operators, right?
We said that the reference operators in the brackets they happen before comparing.
So here and in here we get both false because we are summing B3 to B4 and comparing it to
C3 to C4.
They're not equal.
So we get false and it's not greater than we get false. It's actually less than
so we get true.
But check this out. If you ever want to get advanced in Excel this is great to know. What happens if I
take a false and I add it to a true.
What do you think I'm going to get? A 1. The moment Excel runs any mathematical operation on true and false
values it translates a false to a zero and a true to a 1.
So what happens if I multiply a false with a true? I get a zero because it becomes zero
multipled by 1.
It's a zero.
What happens if I add a true to a true?
I get two. A one plus a one
is a two.
This is something to keep in mind if you ever want to become advanced in Excel. You're going to be
working a lot with comparison operators inside Excel functions.
It's going to save you a lot of time
If you understand this concept. Try to do these in your head
and now let's cross-check results.
These are the values that we get.
I have a bonus tip for you here.
If you're wondering how I get the formulas here in a separate cell. I'm actually using a function called
formula text.
Excel has a lot of great functions and we're going to take a look at some important basic functions
in the next sections. But this comes in really handy if you want to show your formulas in separate cells
as well as your numbers.
If you're giving a training or you're discussing a formula you can use formula text just reference the
cell in which you have your formula and press enter and you get that formula typed here and and it's live.
The moment you change something in your formula.
Multiply this with eight you can see it reflected in the cell as well.
OK, so that was the bonus tip.
Let's move on to looking at simple formulas in Excel.