Original subtitles

In this lesson, we're really going to isolate that if function and take a look at it in a little bit

more detail.

Now we saw in the previous lesson some basic uses of if and how we can use if to add meaning to true

or false results.

Now the ifs that I showed you so far, we've been typing them directly into the cell.

Now that might be your preferred method, but also remember that you can use the functions dialog box

if you find that a little bit easier.

Now you can access your functions dialog box simply by clicking on this f x icon.

And then searching through your functions list for the function that you want, so in this case, if

if I click, go there it is at the top.

Alternatively, I could jump across to the Formulas tab and in my functions library from the logical

group I could choose if.

And that's basically going to open up my arguments, but in a dialog box, as opposed to just having

them appear underneath the formula in the cell.

Now, some people find this method a lot easier, particularly when they're learning more complex formulas.

I'm somebody who prefers to just type it into the cell, but I'm going to show you this example using

the functions dialog box so you can see the other option that you have.

Now, before we get on to doing this, let's just take a look at our data.

So here I have a list of furniture sales for 2018 and 2019.

We have the products listed in Column A. The customer, the date, the item cost, the number of items

they purchased and the total cost and the total cost is a calculation.

It's basically the item cost multiplied by the number of items.

And what we need to do here is complete the shipping column and the total column.

And I can see over here for orders over $1500.

There's going to be a shipping charge of two percent of the cost.

So we need to make sure that we incorporate this calculation into an if formula.

So let's widen out this column just a little bit.

So we have more room and I'm going to click and sell for let's jump up to formulas into logical and

select if the notice.

As soon as I've done that, it's put the word f into the cell and now I can perform my logical test.

So my logical test in this scenario is if the total cost in cell four is greater than 1500 now, if

I had hard coded 5500 into here, the formula is still going to work.

But this goes back to one of those golden rules I was speaking about earlier.

If you do hard, code your numbers into your formulas and then those numbers change, it means you're

going to have to go back in, find the formula and editor and then copy that formula down for however

many rows that you've got.

It's much more beneficial, efficient and safer to use a cell reference instead.

So that is why instead of typing in 1500, I would in general have the amount listed in the cell so

I can simply select the cell.

So this value changes, I only need to update it in one place, and all of my formulas will update automatically.

Now I am going to copy this formula down, so we need to make sure we lock Cell K three so that it doesn't

move f full and notice here in this functions dialog box.

It's telling me what the results of this logical test is for this first row, which is false.

So here the total cost is not over 1500.

Now I can specify what I wanted to say if this is true.

Well, if the total cost is greater than 1500, we need to apply a two percent shipping charge and that

is two per cent of the total cost.

So we need to perform a calculation here.

So if it's true, we want to do the total cost multiplied by two percent.

The result that we have in K four F4 to lock that cell because we're going to copy it down if it's false.

So if it's under 1500, well, there's going to be no shipping charge.

So I can simply put a zero in that.

Let's click on OK and double click to copy that formula down.

Notice for all of the values which are under 1500.

We have no shipping charge, but as soon as we get to one that is over 1500, the shipping charge is

two percent of that total cost figure.

So now just to complete this table, we can perform a very simple sum calculation.

We just need to add together the total cost and the shipping cost control and and double click to copy

that down.

So now if any of this information changes over here, so maybe four orders over a thousand when I hit

enter my table's going to update and I've only had to change it in one place.

If the percentage changes, so maybe it changes to five percent.

I can simply change it here, and all of my numbers are going to update, so everything is very efficient.