Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:04,970 --> 00:00:09,830
In this lesson, we're really going to isolate that if function and take a look at it in a little bit
2
00:00:09,830 --> 00:00:10,910
more detail.
3
00:00:11,780 --> 00:00:18,710
Now we saw in the previous lesson some basic uses of if and how we can use if to add meaning to true
4
00:00:18,710 --> 00:00:19,940
or false results.
5
00:00:20,480 --> 00:00:25,400
Now the ifs that I showed you so far, we've been typing them directly into the cell.
6
00:00:25,910 --> 00:00:31,520
Now that might be your preferred method, but also remember that you can use the functions dialog box
7
00:00:31,520 --> 00:00:33,200
if you find that a little bit easier.
8
00:00:33,950 --> 00:00:39,010
Now you can access your functions dialog box simply by clicking on this f x icon.
9
00:00:40,230 --> 00:00:45,570
And then searching through your functions list for the function that you want, so in this case, if
10
00:00:45,570 --> 00:00:48,240
if I click, go there it is at the top.
11
00:00:49,050 --> 00:00:54,720
Alternatively, I could jump across to the Formulas tab and in my functions library from the logical
12
00:00:54,720 --> 00:00:56,430
group I could choose if.
13
00:00:57,360 --> 00:01:03,420
And that's basically going to open up my arguments, but in a dialog box, as opposed to just having
14
00:01:03,420 --> 00:01:06,090
them appear underneath the formula in the cell.
15
00:01:06,660 --> 00:01:12,030
Now, some people find this method a lot easier, particularly when they're learning more complex formulas.
16
00:01:12,480 --> 00:01:18,030
I'm somebody who prefers to just type it into the cell, but I'm going to show you this example using
17
00:01:18,030 --> 00:01:21,510
the functions dialog box so you can see the other option that you have.
18
00:01:21,810 --> 00:01:25,410
Now, before we get on to doing this, let's just take a look at our data.
19
00:01:26,130 --> 00:01:30,930
So here I have a list of furniture sales for 2018 and 2019.
20
00:01:31,650 --> 00:01:39,360
We have the products listed in Column A. The customer, the date, the item cost, the number of items
21
00:01:39,360 --> 00:01:44,650
they purchased and the total cost and the total cost is a calculation.
22
00:01:44,670 --> 00:01:49,260
It's basically the item cost multiplied by the number of items.
23
00:01:50,040 --> 00:01:54,900
And what we need to do here is complete the shipping column and the total column.
24
00:01:55,170 --> 00:01:59,250
And I can see over here for orders over $1500.
25
00:01:59,520 --> 00:02:03,240
There's going to be a shipping charge of two percent of the cost.
26
00:02:03,600 --> 00:02:08,760
So we need to make sure that we incorporate this calculation into an if formula.
27
00:02:09,690 --> 00:02:12,210
So let's widen out this column just a little bit.
28
00:02:12,210 --> 00:02:19,650
So we have more room and I'm going to click and sell for let's jump up to formulas into logical and
29
00:02:19,650 --> 00:02:22,710
select if the notice.
30
00:02:22,710 --> 00:02:29,310
As soon as I've done that, it's put the word f into the cell and now I can perform my logical test.
31
00:02:30,030 --> 00:02:41,250
So my logical test in this scenario is if the total cost in cell four is greater than 1500 now, if
32
00:02:41,250 --> 00:02:46,080
I had hard coded 5500 into here, the formula is still going to work.
33
00:02:46,530 --> 00:02:50,220
But this goes back to one of those golden rules I was speaking about earlier.
34
00:02:50,730 --> 00:02:56,010
If you do hard, code your numbers into your formulas and then those numbers change, it means you're
35
00:02:56,010 --> 00:03:02,610
going to have to go back in, find the formula and editor and then copy that formula down for however
36
00:03:02,610 --> 00:03:03,780
many rows that you've got.
37
00:03:04,620 --> 00:03:10,440
It's much more beneficial, efficient and safer to use a cell reference instead.
38
00:03:11,070 --> 00:03:17,370
So that is why instead of typing in 1500, I would in general have the amount listed in the cell so
39
00:03:17,370 --> 00:03:19,210
I can simply select the cell.
40
00:03:19,830 --> 00:03:26,580
So this value changes, I only need to update it in one place, and all of my formulas will update automatically.
41
00:03:27,510 --> 00:03:33,630
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
42
00:03:33,630 --> 00:03:38,190
move f full and notice here in this functions dialog box.
43
00:03:38,190 --> 00:03:43,920
It's telling me what the results of this logical test is for this first row, which is false.
44
00:03:44,310 --> 00:03:47,760
So here the total cost is not over 1500.
45
00:03:48,030 --> 00:03:52,110
Now I can specify what I wanted to say if this is true.
46
00:03:52,290 --> 00:03:59,520
Well, if the total cost is greater than 1500, we need to apply a two percent shipping charge and that
47
00:03:59,520 --> 00:04:01,780
is two per cent of the total cost.
48
00:04:01,810 --> 00:04:03,810
So we need to perform a calculation here.
49
00:04:04,620 --> 00:04:11,720
So if it's true, we want to do the total cost multiplied by two percent.
50
00:04:11,730 --> 00:04:19,950
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.
51
00:04:19,950 --> 00:04:24,600
So if it's under 1500, well, there's going to be no shipping charge.
52
00:04:24,600 --> 00:04:26,940
So I can simply put a zero in that.
53
00:04:27,900 --> 00:04:33,180
Let's click on OK and double click to copy that formula down.
54
00:04:33,630 --> 00:04:38,160
Notice for all of the values which are under 1500.
55
00:04:38,160 --> 00:04:44,910
We have no shipping charge, but as soon as we get to one that is over 1500, the shipping charge is
56
00:04:44,910 --> 00:04:47,610
two percent of that total cost figure.
57
00:04:47,880 --> 00:04:53,220
So now just to complete this table, we can perform a very simple sum calculation.
58
00:04:53,640 --> 00:05:03,360
We just need to add together the total cost and the shipping cost control and and double click to copy
59
00:05:03,360 --> 00:05:04,020
that down.
60
00:05:04,440 --> 00:05:10,760
So now if any of this information changes over here, so maybe four orders over a thousand when I hit
61
00:05:10,770 --> 00:05:15,390
enter my table's going to update and I've only had to change it in one place.
62
00:05:16,230 --> 00:05:19,770
If the percentage changes, so maybe it changes to five percent.
63
00:05:19,920 --> 00:05:26,370
I can simply change it here, and all of my numbers are going to update, so everything is very efficient.
6934
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.