Transcripts
1. Formula fun intro: Being able to write
formulas and use functions is a crucial
part of working in Excel. However, is also something that a lot of people
struggle with. But knowing just a few
fundamental things will enable you to use any
function available in Excel. And that's exactly
what this part of the axon boot camp
training is about. Hi, I'm boss and I'm Trainer and Consultant for axial
Power BI and Tableau. I wrote my own company, data training IO, and
I'm also a YouTuber. I've built this complete
online actual training to help you master Excel in the quickest way
without wasting time learning things that you
want to use in practice. But this training is
the second part of the axon bootcamp where
we will talk about essential things and
best practices for working with formulas
and functions in Excel.
2. Creating a simple formula: This section we're
going to have a look at formulas and functions, something that you will be using all the time when
you work in Excel. Now, formulas basically
just let you do calculations like
in a calculator. But in Excel, you
can do much more once you start using formulas in combination
with functions. For example, to
clean up your data. Or maybe you want to extract data using some of
the lookup functions. Now, let's start all the way at the beginning and go
over some basics first, the Excel workbook for
this part you find in 0 to four mana fun and then go to Z11 formulas and
functions one-to-one. Now open up the Excel workbook. And this workbook we're going to start on the very first sheet, which is 01, creating formulas. Now here we have exactly
the same summary overview that we have built in
the previous section, but let's now add a few extra
insights to this dataset. Now, starting off by adding the averages for
the value columns, for example, the booking window, Let's say we would like to have the average booking
window for hotel one. And what I'll do now,
how could we do that? Now here, let's go
to the cell h 11. And here we can do our
average calculation by starting off
with an equal sign. And as soon as you start diving, you will see in the
formula bar and there we have now an equal sign. This is always the
start of a form. Now, if we want to
calculate average, what we could do is sum up all
of these values and divide it by the count of bookings
that we got for that hotel. So we sum up all five values
and divide it by five. This will be one option. Now, how could we do that? We could go and hard-coded
values like this. 35 plus 31 plus 36 plus 50, plus 30, and then
divide it by five. Now what we are doing here is wrong because of two reasons. First of all, the
division happens. First, because division happens before addition and the
sum of the other values. So what happens is a device that 35 and then only as
the other values, so we end up with
a value of 158. Now how can we change the order of calculation with brackets? So let me go back to that. So now we can put in brackets around some of these values
before we do the division. So the sum happens first and
then the division by five. Now we do get the
Gregg value of 36.4. However, still it's
not really good. Now the second reason why
our formula is not so good is because we have
hard-coded device. That means if one of
these values dangers, for example, the first one, let's change it to 40 than my formula doesn't
automatically update. To make sure that our
calculation nicely updates when the underlying
values get updated, we have to refer
to the cells that contain values instead
of hard-coding values. Now, I'm going to change
the 40 back to 35. And let's go back
to the formula. Now over here, Let's get rid
of the sum of these values. And instead of this, we could also just click on the cell from which we
want to have the value. Then again, plus, and then I click on all of
the other cells. And you see each
time when I have a new cell or new range that
gets a different color. So that makes it
very easy to find the ranges that you're referring
to inside of your form. Alright, now let's press Enter and let's see if we still
get the correct result. Yeah, we still have that
thirty-six point four. But if one of the
values changes, then the result of a calculation
also nicely objects. Now you might be
wondering, well, what you're doing here is
not the most efficient way. I've already used axilla bit. And there's the
average function, which lets you do this
much more easily. And of course you are correct. However, here we're starting
with the beginnings normal formulas without
using any function just yet. So we will get back to this very soon when we start
talking about functions. Alright, so now that we
have a formula that works, how can we do the
same calculation for all of the other columns? So the length of stay, number of rooms, nightly
rate, and total revenue. And then also do
it for hotel too. You don't want to
do this manually. Instead of that,
we're just going to copy this out with Control C. Then select the cells where
you want to base it to, and then Control V to paste. And here you see
we have different values for all of the gods. And that is because when we
copy a formula to the right, then all of the cell references, they also move to the right. So here for the length of stay, let's click on it. Then go to the formula bar. Now you see we have the sound references to the
values inside of that column. What if we take a Formulas and copy them down to hotel six? So let's do that. Let's dig or formulas
control C to copy. Then go six rows down here for
DO2 Control V to paste it. And you see also here the cell references
moved six rolls down. So here we have the average
booking window for D2. So this is how we can
do normal calculations using formulas in Excel. Now the next thing that
we're going to have a look at is conditional format, where we can check
different conditions and then see if
it's true or false.
3. Formulas for logical comparisons: We have just seen how to do simple calculations
using formulas. Now in this part we're
going to have a look at how we can do comparisons
inside of our formulas. So basically check for logical conditions
and return true, it's true and false otherwise. Now, let's see how it
works now here we have the same summary
overview from before, but now we're going
to do a few jacks. Now here at the bottom, I have a few things that I
want to check for. For example, the first
one here I want to see if the average booking
window of hotel one is longer than an hotel too. So here is this value
that we have in each Dan bigger than the
value that we have in age 60. Now how can we check for this? Now, of course you
can do it manually, but in this case
I wanted to do it automatically using
formulas in Excel. Because if you need to do this Jacques thousands of times, then you of course don't
want to do this manually, and therefore, we have formulas. Now, let's go to age 19 and again start
with an equal sign, because this is just
another formula. A formula starts
with an equal sign. Now here I want to see if the value that we
have an H done. So click on h tan is bigger than the value that
we have in age 16. This is it. We have a bigger than
sign in-between. Now, let's press Enter. You see it returns
true because the thirty-six point four
is bigger than 31, if we would have a value
below the 31 here. So let me dive in, let's
say 20, done, return false. Now let's undo this Control Z, and let's go to the next one. Now for the next comparison, we wanted to see if the
average length of stay for hotel one is shorter
than orbital two. So over here we are now focusing on the
language day column. And I go here to 20, and that's the next
comparison form, again with an equal
sign we started. And now we want to refer over here to the value
that we have an item. I want to see if it's shorter, lower than the value
that we have in 16. So let's click on I6 and you see the operator site that we have in-between
these two values, these two sound references
is smaller than not. That's presenter is it also
returns true because the 2.8 in cell item is smaller
than the forefront to 60. Okay, so let's keep on going. So for the next comparison, we want to see if the
average number of rooms between the hotels is not equal. So it's different. How can we do that? Now let's go over here to
the number of rooms column. And here we start again
with the equal sign. And now we want to
first refer to data. And now, how can
we do not equal? Not equal. You can do as follows, with a smaller than, bigger than sine, so different from and then we refer to the value
that we have in j 60. Okay? Alright, press enter. And again it's true because
they are not exactly equal. So you see the way
in which we can do these comparisons
using formulas. So as they say, the
only thing that you really need to remember
is the operator science. And so bigger than, smaller
than, not equal to. And then for the next one there, we're going to have a look
at the next operator sign, which is combination of
bigger than or equal to. So here we want to check if
the average nightly rate in hotel one is equal to or higher
than those for auto tune. So here I want to check
if the 325 is equal to a bigger than the average
nightly rate of hotel two. Okay, Now, let's go here to k 20 to start with the equal sign. Then, refer to the value that
we have in the South Keita. And now I want to check if
the value that we have in that cell is bigger
than or equal to, so bigger than
sine equal to sign the value that we
have in cell k 60. All right, answer. That's it returns
true that this works. However, again, imagine
that you might forget in which order to place
these signs in-between. Doesn't the equal sign converse
or the bigger than sign? Now, if you forget this and
do it the other way around, so equal to bigger than,
let's see what happens. I press Enter and
you'll see that Excel recognizes what you tried to do and gives you an
information box, Okay? I believe you are
trying to do this. Let me put it the other
way around for you. Do you accept this? Alright, we accept plain k and that's it. So, yeah, you see axial tries to help you out
here a little bit. Now let's do the
very last one where we want to check if
the total revenue of hotel one is smaller
than or equal to the revenue of order two. Over here, we're going to call them out where we
have the dollar revenue. And here we can do an equal
sign to start a formula. Then we want to see if the
total revenue for hotel one, the 4.7 thousand is
smaller than or equal to, smaller than sign,
then the equals sign. And we want to compare it to the value that we have in L69. Press enter, and this is false. So now you know
how to incorporate a logical conditions
in your formulas. Now, the main takeaway is that you have to remember
these operators science. So let me highlight
them for you. So we have the bigger than side. We have this smaller than sign. If you want to check if
something is not equal, then you do smaller than
n, bigger than sign. If you want to check
if something is equal, then it equals sign. And if you want to check
if something is bigger than or equal to or
smaller than or equal to, you can do the
combination of bigger than sign with an equal sign. And of course this also
works the other way around. So let me copy this down. The last one is smaller
than or equal to. So now you know how to
do normal calculations, you know how to do
comparison formulas. The next thing that we're
going to have a look at is cell references, because there's sometimes
you want to sell references to move when
you copy a formula, but sometimes you don't. And for that, we have to
have a closer look at how we can fix either row or column
inside of our references.
4. Cell references: what's up with those dollar signs: Let's talk about cell
references before you have seen that when we copy a formula
to the right or down, or up or to the left, then
cell references move in the same direction by the same
number of columns or rows. Or sometimes this is good thing. Sometimes you actually want the cell references not to move. Now, here we are going to
have an example that's based on the same summary
overview that we built before. Now I added, however, two new columns over here. Rate after it's gone, one
rate after this going to do. Because I want to check
if we might want to give a discount to a certain person when they stay for longer time. Before we going to do that, we first want to
calculate how much the price would be after
these two discount, okay? Now, the first discount rate that we want to apply is 10%. The second one is 20 per cent. That the first thing that
we want to calculate is the price after the first
discount rate of 10%. And then we do the
same thing for the price after the second
discount rate, we're just 20%. Now, let's start
over here in cell IL-6 and select that cell and start again with
the equals sign. Now, how can we calculate the price after that
Dan per cent discount? Well, we can do the
following calculation. We take the nightly red, Let's click on that case x. And then we want to multiply
that an in-between brackets. We can do one minus
the 10% discount. Close the brackets. And that's it. Now. First, it calculates the
one minus the value in R3, which is Dan percent, so 90%. And then it multiplies that
we have the nightly rate, which is 319, and that should give us
the discounted price. Now let's see, let's press
Enter and it gives us 287. That looks good. Alright, now this calculation we don't want to do
over and over again for all of the other cells
below it and right next to it. So instead of that, I'm just going to copy that and go over here to that cell
bottom right corner, you see my cursor changes and
I drag it down over here. But this doesn't look right. What is going on? Why do we have there and error? And why do these values
look a little bit off. Now to figure out
what's going on. Let me go over here to the
value that's right below it. So l seven. And let's have a look
in the formula bar. Now you see that my reference is more because I moved
my formula down. So now we do not have k six, but we have k seven because
we weren't one cell down. Okay, so I take the next
nightly rate, which is good. However, now the
problematic part, one minus the value
that isn't L4, L4, it's just an empty cell. I still want to take the
ten per cent discount rate. But because my
formula moved down, my cell reference
is also moved down. And now we are selecting
that empty cell. And that's also why the next one returns an
error because one minus, well, the text that we have
here, that's not possible. Okay, So it returns an error. Alright, so how can
we make sure that the cell reference to the Dan per cent discount
doesn't move down. Well, let's go back over here
to the very first formula, and let's remove all
of the other ones. But I go back over
here to our six. Now here we want to fix
the reference to L3, which contains a discount
rate of ten per cent. And that we can do
with donor sites. But the question is, where do we need to put the downsides? Because here we have
three alternatives. We can either have $2 signs in front of the
one in front of three, or only front of it free or
only front of Tao or none. None doesn't work.
However, where to put the dollar signs? Which of the remaining
three options? Now, let's first put both
dollar signs in there. To do this and easy way, you just take your cursor, but it only reference and
press F4 on your keyboard. Then you can toggle
back and forth between all of the
options, okay, Now, I want to have
$2 signs in front of the front of the
free press Enter. And now I'm going to
take my reference, drag it down, and you see, now we do have the
correct values. If I now go again to L7, you see we have K7, okay, that is still the same. However, three didn't move. Alright, so we are still
taking it down per cent. And over here, if I
copy that down here to the button, again, also here, it works, okay, but
what if we want to now do the same thing
for discount rate too? Now here we could take again
the very first formula. Copied one cell to the right. And then over here, 46. Now you see we have 258, which is value that
might look okay. However, actually is not. If we go to the Formula bar
and check the references, you will see that
my reference to the nightly rate now is a reference to the rate
after discount one. And here, what about
the 10% discount? Well, it didn't move to the 20 per cent discount
because they fixed it before. So now, how can we make sure that a formula
works correctly? So that takes the nightly
rate and it started at 10%, would take the 20 per cent. Now you don't want
to do this manually, so we have to check
our references again. So let's go back and
delete over here. And six, Let's go back to our sex and then we just go
referenced by reference. Now, you saw before that
case x turns into L6. Now, to make sure that
the colon doesn't change, you need to put $1 sign
in front of the k. So you always put
the dollar sign in front of what you don't want to move, what
you want to fix. Now here we want
to fix the Golan and not the row number
because when we copying down, that reference should
still move up and down. Alright, then what about L3? Why did it become an A3? Well, that is because we have a dollar sign in
front of the column. So if we get rid of
the dollar sign and only keep a dollar sign
in front of the three. That means the
column is not fixed, but the row number is fixed. Okay? I'm going to press Enter. Now, let's drag it to the right. Let's go here to M6. Is it now it takes the
normal Nike rate and multiply that by one minus the 20 per
cent, so 80 per cent. Okay, so that looks correct. So now that we have
the correct formula, I just copy it from oral sex. Then they said to all of the other cells that we
have over here, okay? And this is a working version of a formula with the
correct cell references. Now, again, to do this
in the most easy way, the shortcut key over here is f, for which lets you toggle between all of the
different alternatives. Now cell references
and working with these dollar signs
where to place them can be quite tricky
at the beginning. So it's time for an exercise.
5. Exercise: where to put the $ signs?: Where to put the
dollar signs inside of your cell references to fix the columns and
rows can be tricky. So let's do an exercise. Now over here on sheet
for exercise there, we're going to do the following. We want to have scenario analysis where on
the left-hand side we have the different
share prices and the projected
growth here at the top. Now, first of all, we need to calculate the share price after the
projected growth rate. Now let's get the
formula to work first before we focus on
the cell references, I go over here to E7 and
start with the equal sign. Here we want to have
the share price 11.19 and to calculate it after projected
growth of minus 5%, so it shrink down, we have to do multiplication. We can say multiply this by
an in-between the brackets, one plus the projected growth rate of minus
five per cent. Close the brackets, press Enter, and that gives us 10.63. Now, how can we now
then copy it to all of the other sounds
without having to redo our formula
over and over again, not to do that in the
most efficient way, we can work with
the dollar signs. Now here for our
first reference, C7, where do we need
to put the dollar? Under the sea or in
front of the seven? Well, if you have no clue, then just take over here the cell and copy at
one cell to the right. Now if we do that, then you
see C7 turns into December. So we need to fix the color. Alright, so I'm gonna
delete that one, go back to our original formula and put a dollar sign
here in front of the god. See. Now, when I drag
it to the right, you see a reference is still C7. Okay. Do we also need a dollar sign in
front of the seven? No. Because when we take a formula and drag
it down one cell, then you see with just
takes the next share price, which is exactly what we want. Alright, so the first
reference is good. Now let's go back to the original formula and
delete the other ones. Now, we have to do the
same thing for E5. What happens when I take
my cell and drag it one cell to the right
if I becomes five, so it moves to the right, which is good because
we want to take the next projected growth rate. However, when we go back to the formula and
drag it down one cell, then you see if I becomes
E6 and that's not good. We want the row
to stay the same. So let's delete the
other formulas, go back to the original formula. We need now to fix
the row number, which we can do with a dollar sign in front
of the row number. Press Enter, and
now it should work. Let's try it out. Let's copy the formula, select all of these
other sounds, and do Control V to paste. And that's it. Now, always double-check that. So just dig around themselves, go to the Formula bar and see if it takes the
correct input value. So we have the share
price over here. You can see we have the projected growth
rate over here in E15. It looks all good. So we've got it working. Alright, so the main
takeaway, first, get the formula to
work and then just go sell referenced
by cell reference. And if you don't know exactly where to put the dollar signs, just try it out, drag it wanted drag one down and see
how the references change. Okay, So far we've talked about how to do calculations in Excel, how to incorporate
logical conditions, doing comparison formulas, and we talked about
cell references. Now, the next thing
that we're going to do is talk about functions, which makes our
life a lot easier.
6. Aggregation functions: get more done more quickly with functions: Now it's time to talk
about functions and function stake your
calculations to the next level, but not only normal
calculations, it also lets us do other
things like data cleaning or extracting data
using lookup functions. Alright, but let's start
with the beginning. Now, here we're going to go back to a summary
overview from before. We do that average calculation because it was not the most efficient way
in which we did it. We can do better than
that using functions. Here. We wanted to have
the booking when the average for both
of these models. So let's go back to H then H, then we can start with the
equal sign, just like before. But now we're not
going to refer to every single cell and
then divided by five. But instead of that, we
use the average function, which lends us do this average, average calculation
in a much easier way. So let's start typing
AV and I stop. Don't type in the whole name of the function instead of that. Tried to just start with a V. It gives you a list of all
the functions that start with a V and then go to
the one that you need using the arrow
keys, not to select it. You could use the
mouse but try not to. Instead of that selected
by pressing the Tab key. Alright? So you see when you do that, then it capitalizes
the function name, already has the opening bracket, and then it tells
you what it needs. Now, always pay close
attention to the syntax. So the structure of
the function here, its average function,
relatively simple. It just needs one
or more ranges. Now where is my range over here? The booking window
values for that one. Alright, then we can
close the bracket or just press Enter because
Exxon knows what you're trying to do and it adds the
closing bracket for you. And again, we have
exactly same value as before, the 36.4. So that looks good.
Alright, now we want to do the same calculation
for all of these articles. So how does that work with the references? Doesn't
work in the same way. Let's, let's try it out. Okay, So I'm going to
copy over here to cell, then select the cells
where you want to base their formula to Control V. And we have the same
results as before. The cell references. They also moved to the right. Now can do the same thing again, but then vertically
copy the cells, but down to about L2. And now you see we have
the same values as before. Alright? So what if we don't want
to have an average, but maybe a sum or count. Well, it works kind
of in the same way. Let's take, for example,
the total revenue. Let's select all done. And instead of the average, we could also have
done some function, some selected by pressing Tab. Then also here we just need
one on multiple ranges. So here, the range that
I want to refer to, all these values above it, the press Enter, and
now we have this up. Then I can copy it, paste it below here for D2. And there you go. What if we
want to have the account? Well, then in a similar way, we could go over here
instead of some function, we could go for a
GAN function and you will see discount, discount, a discount blank,
all different kinds of variations that we
can have a look at. However, for now,
let's just go for a normal count and then
the range stays the same. Press Enter, see it
returns, of course five. Okay, so this is how we can do simple summary aggregations
using functions in Excel.
7. Logical functions: how to handle more complex logical conditions: So now that we know
how to work with some basic aggregation functions
like sum, average count. Let's have a look at
some logical functions. Now the first ones that
we're going to focus on are the add function
and our function. So let's go back to our
summary overview and extend our analysis by making some
more complex comparisons. This time, here at the bottom, I have different things
that I want to check for. Now, before, when we were talking about our
comparison formulas, we had just one comparison, one logical condition that
we were checking for. However, now this
time we're going to have two or more than two. So let's see how the AND, and OR function can
help us do that. Let's go here to the first one. We want to check if the average booking window and the length of stay for hotel one is
long enough for water to. Alright, so two
logical conditions. Now, here, I want to check if both of these
conditions are true. Only when both are true. I want to return
true. And that is what the n function lets us do. Alright, now, let's
see it in action. Let's go over here
to the cell age 26. And just like before we
start with an equal sign, here, we're going to
write an AND function. So type in and select
it by pressing tab, followed by the
logical conditions. Now, here it just needs
multiple logical conditions. Logical one is going to be
whether the booking window, the average booking
window for that one, is bigger than the average
booking window for D2. So here we can do it exactly
in the same way as before. So I select over h, Then I want to see if
it's bigger than age 60. Okay, so just like before, but now it's just the first
argument within the function that we can add multiple conditions inside
of this add function. So to do that, type in a comma. And now you see over here
and logical two is in bold, which means we are
now going to define the second logical condition
we want to check for. So here we want to see if the
average length of stay in item is bigger than 16. Average length of
stay for up to now, we have two conditions, but we could add more
if you wanted to. And then close the
brackets and press Enter. Now here, this is false. Why is it false? Well, the first logical
condition was actually true. Thirty-six point four
is bigger than the 31. However, the second
logical condition, 2.8 is not bigger than 42. And because not both logical
conditions are true, our end function returns false. So if over here
we would not have 4.2 for the average length
of stay forward to, however, maybe a smaller
number like one. Then it would
return true because now the second logical condition
would also return true. Then both logical conditions are true and the N function
would return true. Okay, now I'm going to undo this so that we have for
point to here again. And let's have a look
at the next one where we have a similar comparison. But this time we
want to check if the average booking
window for hotel one or the average
length of stay for which one is bigger
than the one for D2. Now, we're not going to
use the AND function, but the OR function because
now we just want to see if one of these
conditions, It's true. Alright, so let's go
and try this out. So I'm gonna go
over here in H 27, start with an equal sign. Here we can start
with the OR function. Now here we want to see again
if h is bigger than age 60, done the second
logical condition, logical to as bold. And then we can refer to item and see if it's
bigger than I6. Okay? Close your brackets. You
see the logical conditions are the same as 40 and function, but this time it returns true. The reason it returns
true is because, well, one of them is true. The first logical
condition over here, It's true 36.4 is
bigger than 31. And the second one is not true. But this time we have
our function or, the, OR function returns true if one of the conditions is true. So the next one, you can
first try on your own. So pause, try them out and then continue watching
or just follow along. Alright, so comparison
number three. So here we want to
see if the average nightly rate and the
total revenue for her than one or equal to or
higher than for host of two. So let's go over here to j 28 and start with an equal sign. Now here we're
going to use it and function logical one is if the nightly read AND gate n is bigger than or equal to, so bigger than or equal to sign. And then refer to 60 than the other tasks
that we want to do is, I'll turn bigger than
or equal to L 16. That these are two logical
conditions in both need to be true for the AND
function to return true. And indeed, that is the case. So the end function returns
true because here we have 325 bigger than 984,786 is
bigger than the 2964. Now what if we would have
used the OR function instead of the n function? Let's try it out. I'm not going to retype
the whole thing. I'm just going to copy it from the formula bar, Control-C. Go to the cell below, and then I go to the Formula bar
basically in there. Why didn't I just drag it down? Because then the cell reference
is also moved down and then we have to burn them
at the Dollar science. And I wanted to skip
that part for now. Okay, so press Enter. And over here we have at the
moment the same function, but I'm going to replace the add function with
the OR function. Okay? Now, this is also returning
true because, well, at least one of them is true, but actually bothered to read
this, but at this point, but what if the dollar revenues for older one would fall to, let say 1 thousand, then only the first condition
would have been true. Then the function returns false, but the all function
will return true. Alright, so now you're able to do more complex comparisons. But probably you don't want to just return true and false. Usually you want to do maybe one calculation if it's true and another
calculation if it's false. And for that we need a function which is going
to be the next part.
8. IF function + nesting functions: If within an if, within another if: So we are exploring
different logical functions and we have already seen AND, and, OR function to make
more complex comparisons. But the next one that
we're going to have a look at is the f function. And f function is one of the most used functions with an axon because it's so useful. And basically it just comes
down to the following. If this done that, now let's see how
it works in action. So let's first start
with a simple example. Let's go over here to the colon. And a final goal is to
calculate the actual rate that they customer has to be on the basis of
the length of stay. So if somebody stays
for a more nights, then this person gets the rate after discount to 20% discount. If somebody stays two
or three nights done, this person gets the
rate after this one. And if somebody stays
for just one night, they have to pay the full price. Okay, Now, let's get
there step-by-step. Let's go to the cell and six. Now here we can start
with an equal sign. And let's first check
for a condition. Let's see if the length of stay is equal to
abandon for now, this is something we
have done before. So we can refer here to I6 and check if it's bigger
than or equal to four. Press Enter here. That is true because
the length of stay on the first row is five. Alright? Now, this
is a comparison. Comparisons, these
logical conditions is what we need inside
of a function. So let's go back and right
after the equal sign, we're going to add an if
function f bracket open. Now what is my logical test? Well, we just wrote and logical test our
comparison formula. Then we go with a cursor to
the end and add a comma. Now we go to the next
part of the function. For the next argument, we have to say, what
do we want to return? If that condition is true? Then for third argument,
then we can say, what do we want to return if
the condition returns false? Alright? Now, if it's true, then we can return, well, the rate after
that's going to. So over here, let's click on rate after
they've gone through. Now, you see that these arguments aren't in
between square brackets, so the optional,
so if you wanted, you could already closet
IF function here. Now it returns 255. However, if the length of stay would have been below four, so that's three, then
we'll return false. Alright, let me change
it back to five. There you go. Now let's go back
to our function. Now, if I want to return
something different than false, when the condition
returns fonts, then we can add a
third argument. So we could say if somebody stays for less than four nights, then they pay is the rate after they've gone
one, this one over here. Click on cell, press Enter, and let's check if it works. Changed language,
Dave, back to three. And now we would have
the actual rate of 287. I'm going to change
it back to five. You see the IF function
is actually quite easy. However, were a lot
of people struggle is when you need more
than one IF function. So you need to
nest IF functions. Now, when is this necessary? When you have more
than two outcomes? The number of IF
function depends on the number of outcomes
that you have. For example, if we
have three outcomes, then it's three minus one. You would need to f functions. If you have four outcomes, then you need four minus
13 different functions. Okay? Now, here, let's add
a third outcome, which is going to be that if somebody books do a free night, they get the rate
after this going on. And if somebody
books for one night, then the nightly rate. Okay, so let's go
back to our function. Let's go over here. And where we need to go
is to the arguments, the third one, value if false. Now let's, instead of
ourselves, let's delete it. And now a function basically
says the following. If the length of stay is
bigger than or equal to four, then return the rate
after that's going to do. But if not, well, then we're not sure yet, either denial rate or
the rate after this one. So we need another IF function. So let's type in F. Now here the logical
test is if somebody is state two or three nights. So we're going to
use NOR function. We want to check if the length of stain
that we have units x equals two comma.
Click on it again. I say x equals three. Close the or function. Okay, Now the OR function will return again, true or false. Now, if returns true, then we want to return
the rate after this one. And if that is also not true, then there's only
one option left. That person stays
only one night. So we want to
return the 90 rate. So now we can close
the f function and you see we have
another closing bracket for the outer ear function. Okay, Now, let's
see if that works. We have now a rate of 255. Now, if I change the
length of stay to, let's say one, then we have
the full price of 319. And if I change the
length of stay to, let's say two, then we have the rate after
it's gone, 1287. Alright, so the function works. And just like before,
we can copy it down by taking the sound
control C to copy, and then Control V to paste
it to the other ones. Not only want to
paste the values of formulas that clicking on the Control button and choose that you want to
have only the formulas. And then we can do
the same, yeah, To button copy the formulas, the formulas as format. Now let's go back to our
initial formula where we started here and six. Now here we can go to the
Formula bar and we can expand that a little bit by clicking on the drop-down arrow down
the right-hand side. And now we can divide
it over multiple rows. So we can place the cursor right in front of the f
function Alt Enter, and then it goes to
the next line and just divide it over multiple
rows like this. And if you want to, you can also indented with the space bar. And then you do the same for all of these other arguments. So here, after the comma, I would put that also
in the newline, etc. Now you can just make
a phone number a little bit higher and just keep on going like
this until you have it formatted in the
most readable way. What is the most readable
way while the bathroom, I probably would formula
like this over here. So over here, each
argument on a new line, once you have it formatted, becomes much more readable as actually also
easier to write in this way because you see exactly where everything starts
and where it ends. And if you're missing a bracket that pointed out very quickly, I'm just going to press Enter
and you see it still works. So placing parts of a
formula or function on separate lines and working with spaces doesn't affect the
functionality of your formula. Now let's do then also
the finishing touch, and let's add the
dollar revenue. Here. We just want to
have the product of the actual rate that person needs to pay and
the length of stay. So we can do this by
taking the actual rate, multiplying this with
the language day. Now we'll just drag it down. And then over here, we do
not want the formatting, so say fill without formatting, and then we can also copy it over here to the other cells. So this is how you can
use an IF function. You see the basics are
pretty straightforward. However, as soon as you need to combine multiple F's and maybe also with other
comparison functions like orange and then
it becomes complex. So it's time to practice
with the next exercise.
9. So more practice with the IF function: But just have seen how the
function works and how to handle multiple outcomes
by nesting IF functions. Now let's do another exercise to practice a little bit more, starting off with a
simple IF function with just two outcomes. Now here we have a
dataset where we have the sales for
different countries. The dark details and the
comparison between the two in form of the difference
in percentage difference. Now, if we want to check if the target cells are
above the actual sales, well then we could do
that with a function, but actually we don't
need an IF function yet. We could also just use a logical comparison
starting off with an equal sign here
and I5 and then see if the actual sales, It's bigger than
the target cells. Okay? That gives me true. Alright, I've done
it differently. We could also have looked at the different
percentage difference. Now, let's go back to a
formula and start with an equal sign and then refer
here today percentage. We can check if this
is bigger than 0. So that's 0. And press, Okay, now that
is true just like before. Now here just pick one
of these variations and then we can copy this
formula to the other cells, control C, control V. So this works. However, we want to return
above diagonal below target, not just true and false. So let's go back to
that initial formula. And here we need
now an if function. So if this logical
condition is true, then return above target,
otherwise below target. So right after the equal sign, I'm going to write f
bracket open logical test. Well, we just wrote, so that is already there. And then we can place a comma right after
the logical test. Now value if true above target. Now here we need to put text always in-between
quotation marks. So above docket for text, always in between
quotation marks for values don't need it. The reason yet you need to put this in between
quotation marks. It's otherwise, it
thinks that you're referring to a named range, which is something we
didn't talk about just yet. Okay? So just remember, use quotation marks when you
want to return text, okay? Now, what if it's false, then we want to
return below target. So also here in-between
quotation marks below target. Let's close our f function. You see now instead of true, it returns above target. And there where we
had false before, it returns below target. So that is working.
But let's now make it a bit more complex. Let's add a third outcome. Let's say we want to check the actual sales
versus a target. And if we miss the target by more than 20 per cent,
that's very critical. If it's somewhere between
10, 20%, that's critical. And everything above it. We don't care. It's okay. So three outcomes. Now, let's go here to J five. And it's diving in our
first IF function, if at first logical condition. Here, I usually start with
the most extreme outcomes. And so here I start with the outcome be low or equal
to minus 90 per cent. So again, Jackie had a percentage column after
I started with a function. So f bracket open logical test, H five is smaller than or
equal to minus 20%, okay? Now you can say
minus 20 per cent or minus 0.2, That's the same. Okay, so don't forget
the minus, right? Minus 90%. Alright, then we want to go to the next argument
value of true. Well, if this is true,
that is very critical. So very critical stacks. So I placed it in
between quotation marks. What if it's false? Well, before we define that, Let's just close
the bracket and see if this works by center. We see here it returns false. But when I drag it down, then here you see we have
two times very critical, minus 36, minus 40%. Okay, so that works. Now, the next thing
that we need to do is what if it's better
than the minus 90%? So above isn't then
critical areas that okay, there are still
two outcomes left. Okay, so we go back to
that very first formula. And then let's get rid
of that closing bracket. Let's extend it further and
define the value if false. Now here, we still
have two outcomes, so we need another F, So F bracket open logical test. Now we want to check if that percentage
that we have here, if it's below or equal
to minus Dan per cent. So also here, either 10%
minus 10% or minus 0.1. Now, if this is true, then it's somewhere between
minus ten and minus 20%. And then we want to
return critical, that also here it's taxed. So put it in between
quotation marks. And when it is not below minus 10%, that
means it's above. And the last outcome
is, that is okay. So let's just type in, okay, close the brackets
for that they function. Close the other bracket for
the outer ear function. Presenter. Alright, now we can copy. Now. I see we have okay, most of the times, but here
we have still very critical. And we have one where it
says minus 12% critical. I see the other one
that's 12% plus, which is okay, that means
the sales is above the dog. Okay, now let's go back to
that initial formula again. Let's have a closer look. Now also here we can
extend the formula bar and place it on multiple
lines if you wanted to. However, for now, let's leave it like this and go through it. Now here, the outer logical task that gets performed first is this one here is the percentage value
below equal to minus 0.2. In this case, it's not. So it continues
with the next one. Here, value of
true gets skipped, goes to value if false, then we have another function. If it's between the
minus ten per cent and the minus 0 tool by in-between,
equal to or lower than. Well, we already checked yet in the first condition whether
it was lower than 0.2. So that means if
there is a value that is below or
equal to minus 10%, it must be somewhere in between. So we don't necessarily need to write an
OR function here. If it's equal to a loan and
then Present Dan critical. That is also not the case. There's only one
outcome that's left. That means is high
in n minus 10%. And then we're done. If you're just starting
off with IF functions, you probably need to repeat this a few times before
it really clicks. Also try it on your own data, but practice makes
you master it. The next function that
we're going to look at is the f function. So plural. Now, here we
were nesting IF functions. Now if you find that a
bit difficult than maybe you like the other alternative
more, that's overlooked.
10. IFS function: maybe easier?: We have just seen how to write logical formulas that
use the IF function. Now, if you have more
than two outcomes, then we need to nasty one
if inside of the other, if they need as
many IF functions as you have outcomes minus one. Now, this can become quite complex when your number
of outcomes grows. However, there's an alternative
that you might find it easier and that is to
use the ifs function. Now let's see how
that one works. Now here we have exactly the same example as we had before, but now let's go here to column J and delete the
formulas that we wrote. Now we're going to rewrite
them using the ifs function. So let's go here to J5, type an equals sign, and open
up with the function name. And when you type in F, you
see there at the bottom, then we have Fs here, checks whether one or more
conditions are met and returns value corresponding to
the first true condition. It sounds good. Let's select it. Now here have a close look at
the syntax of the function. Now, we need logical, That's one, that's
the first argument. Then the second argument
is a value of true one. And then we can continue like this logical test
to value if true. There's no false
argument like you have in the IF function. Now, let's see it in action. Let's dive in the
first logical test. We want a dust if the percentage
that we have over here, if it's lower than or equal
to minus 20 per cent. Now, if this is the case, then it is very critical. So quotation mark and
then very critical. So now we can continue
with the next scenario. Now here I want to check if it's somewhere between minus
ten and minus 20 per cent. Now, here we could do that
with an unconditioned, but because this is the second condition
that will be checked, we can simply write
the following H five and then lower than or
equal to minus ten per cent. Now, if this is the case, then we want to return Critical. So now we have defined two
scenarios with two outcomes. Now at this point, if this
would have been an f function, there would be
still an argument. If false, then I want
to return the text. But with the ifs function,
you cannot do that. You still need to define
the last logical test. So here we need to say, okay, if it's five is bigger
than minus ten per cent, then we want to return. Okay? Alright. Now let's press Enter and
you see the first one. Here, it returns. Okay? And if I drag this down, we have here very critical, very critical, critical,
just like we had before. Now also here with
the S function, it is very helpful if you
divide it over multiple rows. So let's expand
that formula bar. And let's place the f
function on the next line. And that's added the
formatting a little bit. And let's put each logical task or an outcome on a separate row. Now, over here, the
closing bracket, I also put it in a new row. And then let's indent each line a little bit that goes
inside of this function. Alright? Now, you see it's also
much easier to read. So now you have two
alternatives to deal with logical formulas where you
have more than two outcomes. Either you go for
nested IF functions, or alternatively, you go
for the ifs function.
11. Exercise: cell references, IF function, and returning blanks: At this point, you
know how to use the IF function and you know where to put
the dollar signs for his cell references. Now let's combine the two
in the following exercise. Follow me to the sheet
09, cumulative sum. And here we have to come
for a year, the month, and we have a value column over here, not accumulate some. What is that? Well, that is
just the accumulated values. So for January is 850, type in here, even 50. Then for February, we take
the 850 in the previous cell, we add the February value. Now I could just
take that formula, drag it down here, and that is the accumulated. So that works perfectly. However, let's say
that you really insist to use some function. How could we rewrite this? Now let me just delete all of these values
that we go over here. And let's go to the
February value in R5. And instead of having
a four plus five, I'm going to use
the sum function. Now, I want to
take this arm off. Well, these two values here
for January and February. Now we can take this formula and drag it one cell belt and then let's see what happens. You see that we are referring not to the cells
January to March, but only February to March. So arrange moves down when
we copy a formula down. Now how can we make sure that we always started in January? Well, dollar signs. Now where do we need to
put the dollar signs? But let's go back to our
original formula here in R5. And we need to fix
the row number for the cell reference E4 because we don't want that
row number that change. Now what about the column while here we don't really care. We just copy the formula down. So we don't necessarily
need to fix the column. So we are going to put the
dollar sign in front of the row number four. Okay, and that's it. Now I can take my
function, drag it down. So here we have the
same result as we had before where we didn't
use the function. But you see how we can fix also here inside of our functions, the references using
the dollar signs. Okay, now, why did I say we
need a function here as well? Well, because the next thing
that I want to do is for dose months for which we don't have the
actual values yet. There I also don't want to know show the
year-to-date sales. Okay? Now how can we make
sure that nothing shows here when there's no value
in the amount column. Well, with the f function. Now again, let's go back
to the cell where we wrote the original formula
and delete the other ones. Now let's write an if function
here at the beginning. And what is the logical dust? Well, here we want to see if there is a value for that month. So we can just refer
to E5 and see if it's different from nothing and nothing you can write with quotation mark, quotation marks. So you are saying you
want to return tags. However, there's nothing in
between, so nothing, alright, now, if it's different
from nothing, then I want to return, well, the year-to-date sales because
that means there's a value and otherwise false there
at the end of the function, then we want to return
nothing quotation marks. Alright, that's closer
function presenter. And you see we have
1750, so that works. And what happens when we drag
it down? It's still works. But here for the
month July onwards, where we don't have
any value just yet. They're also know year-to-date
sales value shows up. Alright, so that was
a small exercise with the f function and
the dollar sign. So you've seen now that you
can use the dollar signs also for references inside
of a function. And that function can also be very nice for aesthetic reasons. Then the next topic that
we're going to have a look at is conditional aggregation. So the gown, the function, the average if function
and the summary function. Let's go to the next topic.
12. Conditional calculations: COUNTIF(S): We've seen how to do
simple aggregations with functions like sum,
average, and count. And we have seen how to build in logic inside of a formulas
using the IF function. Now, the only thing
that we need to do now is put the two together. And we are able to do conditional
calculations like some f average if and count them. Now, we're going to start off
with the count IF function. We're going to start on. She'd done multiple
criteria counting, where we have a
simple dataset with sales data for different
months and years. And the first thing that
we're gonna do is count the number of months where we had sales equal to a high-end. And so we cannot
simply do a count because account will give us just the number of months
for which we had sales data. Now, here we need
to have a count. So let's go to cell H4. We start with an equal sign, then we debit account. And here you see all of the functions that
start with God. The one that we need is count F, So counts the number
of cells within a range that meets
the given condition. So let's select it
by pressing Tab. Now here we need to define the range in which we want
to perform the count, which is the sales column. Let's select E4 all the
way down until e 27. That is the first argument. Then we go to the
second argument where we need to define
the criteria itself. Now the criteria always it goes in-between quotation marks. So let's start with
a quotation mark. And then we put in
the criteria which is bigger than or equal
to a thousand. And then again quotation mark, and then we can close
the count IF function. You see that we're doing seven, which seems to be right
because over here we have six months in 2019 with high-end
thousand cells and just one month in 21. So now we can go
to the next one. What if we want to
check for two criteria? So what if we wanted to
check if the sales amount is equal to either thousand
and the year is 2020. Well then we can use
the count function. And also here you see it
counts the number of cells specified by a given set
of conditions or criteria. Now, this can now be
multiple conditions. So let's select it
by pressing Tab. And you see that the syntax
is just a bit different. Now here we start with D, with Tyrian range one. That's just like before
the sales column. Then we go through the criteria. Here we can say bigger than
or equal to a thousand. Then we close the
quotation marks. Now, we continue with the
second criteria range. The second criteria range is
going to be on the color. So over here at the
beginning of the dataset, and then euphoric with diarrhea. We can put in between quotation
marks, the year 2020. Close brackets, press Enter. You see it just
returns one because we only have one month in 2020, we have sales over a thousand. Now you might be wondering,
what does that actually the purpose of sum F because couldn't we just go
to the very first one and turn COUNTIF into account. Let's try this. Can you see
it returns exactly the same? And that's why personally, I only use COUNTIFS even if
there's just one criteria, range and one criteria, because you never
know in the future, you might change your mind
in a second criteria. So now that we know how to count if income X function work, Let's go to the
next calculation. Here we want to count the
number of months where we had sales over a thousand and where the month was
either May or June. Now, how can we handle
that or condition? Well, let's start again
with our COUNTIFS function. And now we have
just like before, sales as a criteria range. And we have the
criteria which is bigger than or equal
to a thousand. And now the next criteria
is on the month. So I'm going to select
here the Month column. And for the month we
have May or June, but let's just type
in May for now. And then close the quotation
marks and Bracket. Okay, now, let's press Enter. It gives us one because there's only one way where we had a
sales amount over a thousand. Now how can we say or June? Well, to keep it simple
and we could just copy the formula plus sign and just repeat it and
then add the two up here. I just have to change the month
of May to the month June. And that gives us two. Now if you would
have many months, then of course you can expand the formula bar and just place this on the next line to make everything a little
bit more readable, just like this is, there may be another alternative where we don't have to repeat
the whole function. Well, yes, there's
using arrays now, it's a little bit early
to talk about airplanes, but let me show you
an example here. Now instead of having this year, I'm gonna go to that
criteria where we wrote me. And here I'm going to have
a curly bracket open. Then May, which was
already there, comma. And then the second value that I want to have
inside of this array, which is going to be John. Close the array with the
curly closing bracket. Now we can press enter, you will see it gives
us spell that spills over to the other
cells because it returns actually
more than one value. Now, let me show you,
I'm going to copy this. I'm going to base
it below over here. And this anti cell,
you see we have here the value one and
over here the value one. So it returns the count
if may over here. And it returns the count
if june over there. Alright, now, the
only thing that I still want to do
is sum them up. So let's put this
inside some function. Now. This gives us two. So this is an alternative
using arrays. Now I'm just going to
copy this over to where it originally wanted
to have it go. There you go. You might also be wondering, why do we actually need
these quotation marks? Well, this is something
that you need to remember, but let's say you forget,
what can you do that? Well, let's go back over here to that initial formula all the way at the top and age for now, let's say that you are
writing your account, this function, just like this. Then at this point, you could also click on the
insert function button. Now if you do that, then a pub-sub, There's hopper window. And from here you see all of the arguments of this function. And here below, you see what it actually means
where the description. So here we have
criteria range one, but we already
selected a criteria. Then over here we have
the theory itself. Here we could, for example, filling bigger than or equal to thousand without
the quotation marks. And once you are done with setting this all up,
you just click on Okay. You see it puts it
automatically for you in-between these quotation
mark and closes the function. So now it's time for
you to practice. Try to do the remaining
three yourself. Once you're done. In the next section,
we're going to talk about sumifs and averages.
13. Conditional calculations: SUMIF(S), AVERAGEIF(S): We have just seen how to
do conditional counting using COUNTIF and COUNTIFS. But of course there's also some F or some
hives and averages. Now, let's see how they work. Now here we have the same
dataset and we're going to do the following calculations
that you find over here. Starting off with finding
this sum of sales for January 2019
and January 2020. So all of the January's,
how can we do that? Let's go to age four and
let's dive in the sun. And you see all of the
functions that start with some. And in this case we want
to have this sum f. Now here you see we have
one criteria extra, if you compare that
to count them, because now we do not only
have theoria range equity, we also have the range of
which we want to take this up. Now here it just says range. Well, is that the criteria
range or the sum range? It's the criteria range because the last argument,
That's the summary. Alright, so in this case
we need the Month column. Then what is the criteria that in-between quotation marks? This is going to be
the month, January. And the sum range, that is the sales
column over here. Press enter and say 1600, which is the sum
of the January's. So 750 plus the 850 over there. Now what if we don't
want to have the sum but the average for the
January's instead. Well, then of course there's
the average f function. So let's go here to age five. And now type in average. Use the arrow keys to go down
and the tab key to select average F. Now we just
repeat or which is net. So here we have the Month column with diarrhea is going to
be again January. And now the average range, so that is the sales column. And we have now an
average of 800, the average of the 758 on
the fifth. So that works. But what if we need
multiple criteria? Then we have some F's
and average f function. So over here for the next one, we could write some
F's and you see the syntax changes a bit because now we start
with the summary page, not the criteria range anymore. So first the sum
range wages, sales, then the criteria image
while we want to have, let's say, the
January month column. And then he had a good theory
is going to be January. Then we have the second range, which is the year column. Now here we want to
have the year 2020. Alright? So you see criteria range, criteria, criteria,
range criteria. And in this way you
can keep on going. Close your brackets
once you've done. That gives us 850, which is just that
value for January 2020. Alright, so that works. Now, if you want to make it a little bit easier
for yourself, just expand the formula bar and divide it over
multiple lines. So over here there's
some Fs bracket open. And then here we
have the sum range. There may be all of the criteria ranges with the criteria
you put on separate roles. Alright, so over here, you can also indented
a little bit. And all of this makes it a
little bit easier to write. It gives you a better overview. It's also easier for any
person checking your formulas. Now, if we want to
find the average, of course we can retype
the whole thing or simply copy what we
just wrote over here. Copy this. Why don't I just drag it down
but copied actual formula. Because if we drag
it down one row, and then over here
you see the range also moves one row down. We would need to
add the dollar sign so that the range
does it move down, which is something
I don't want to bother with at the moment. So here for age seven, I'm just going to
get rid of this and copy paste the formula
from the cell above. Alright? Then we just changed
some Fs to average. Alright? And it gives us the average. Now what if we want to have
the total sales in 2021? So for January,
February, and March, then we can repeat that
same function three times. Once for January,
second time for February, and then March. So you would literally just copy paste this over
here to the next cell. And then plus. And then over here we can
just take this top part, go to the next row over here. Once more. Let me just expand the
formula bar and then change January over
here to February. And too much. That can happen
sometimes that it gives you a correction and asks you, do you want to accept that? Well, why did I
get it over here? Because I added a
plus sign in the end, which was not really necessary. So double-check before you click Okay, everything looks fine. And now I just make the
formula bar smiling and easy. 2500, which looks correct. And what have we don't want
to have some averages. What then we just take the average or
average IF function, the last one, average
sales in 2019 Q2. So that means April,
May, June, 2019. How can we do that? Well, it is an average, so we need to start off
with an average f function. And then over here we
have the average range, which is going to be the
average of the sales. And then we can adequate theory and just
like we did before, we want to have the year 2019 so they could theory
arranges the year column. Then the criteria is
going to be 2019. And then we can continue
with the criteria range to which needs to be
based on the month. Now, let's select the month
column and then the criteria. Now, the first option here
is to just dive in April, the first month of that quarter. Close your brackets. 1217, then repeat this
for May and June. And then of those values, again take the average. Or alternatively we can
use these arrays again, which you might want to try, but don't worry if it doesn't work out at this point just yet, but just so that you
have seen it again, we could go here just right before the
quotation marks of April, curly bracket open, and then save for which ones do
you want to have it? So April, then we have me, and then we have June. Alright? And then
closing curly bracket. Now that gives us three values. Basically it just returns the average for
each single month. So you will see
if I press Enter, it spills over to the
cells right next to it. Therefore, it's
not very visible, so I'm just going
to put it over here once or lower than you
see the actual values. Here we have the three values. Now we want to take the
average of those three. So I'm gonna go back to
where it says spill and just wrap the whole thing inside
of a normal average function. Okay? It takes now the average of these three values you
see below over here. Now let me delete
those and go back. Now to end this section, let's take a normal average
here of the cells over here, average of the sales column, which gives us 701, but that includes
the zeros over here. What if you don't want it? Well, Try to think
of a solution. And do you have a solution? Yes, average F, Exactly. Now let's go to cell E3
and type in average f's. Now here I always go for it, if not for the F version. Alright, so what does
the average range? I want to take the
average of the sales. And then we add the
criteria range, which is gonna be
exactly same column. And the criteria is
just that it needs to be different from 0. Alright, let's close
the quotation mark. Close the bracket. And you see that
value is considerably higher because it doesn't consider the zeros
here at the end. Alright, so now
you know how to do conditional calculations
using SUMIFS, AVERAGEIFS, and counters. It's time to
practice a bit more.