1
00:00:01,030 --> 00:00:05,310
Let's take a look at some simple but useful formula examples.

2
00:00:05,320 --> 00:00:11,620
This is also going to help us practice entering formulas in Excel. In the first example we want to get

3
00:00:11,620 --> 00:00:15,730
the final value after the percentage increase or decrease.

4
00:00:15,730 --> 00:00:19,850
Here we have a list of items, we have their price and we have the discount

5
00:00:19,870 --> 00:00:21,910
that we want to apply to that price.

6
00:00:21,910 --> 00:00:24,180
We want to get the final value.

7
00:00:24,220 --> 00:00:30,240
First off let's just start to calculate what is the dollar amount of that discount.

8
00:00:30,340 --> 00:00:31,990
Just type in the equals sign.

9
00:00:31,990 --> 00:00:37,330
Go to cell B5 multiplied with the discount and press enter.

10
00:00:37,330 --> 00:00:40,540
Six dollars is the amount of the discount in this case.

11
00:00:40,540 --> 00:00:44,120
Now we want to deduct the six dollars from the original price.

12
00:00:44,230 --> 00:00:46,540
So let's go and modify the formula.

13
00:00:46,540 --> 00:00:50,130
Go to B5 minus our discount value.

14
00:00:50,170 --> 00:00:56,410
The best practice is to input your brackets here so that it's clear which calculation occurs before

15
00:00:56,410 --> 00:00:57,220
the other.

16
00:00:57,310 --> 00:01:03,250
But in this case it doesn't matter whether you have brackets or not because remember multiplication

17
00:01:03,250 --> 00:01:05,500
occurs before subtraction.

18
00:01:05,530 --> 00:01:08,230
When I press enter, I get 54.

19
00:01:08,230 --> 00:01:16,680
We can also calculate this in a different way so we can just factor out B5 and rewrite this formula.

20
00:01:16,690 --> 00:01:24,520
This would be =B5*(1-C5).

21
00:01:24,520 --> 00:01:28,530
Can I safely remove the brackets?

22
00:01:28,550 --> 00:01:29,180
I can't.

23
00:01:29,180 --> 00:01:36,230
I will get the wrong answer because in this case I need the subtraction to happen before the multiplication.

24
00:01:36,740 --> 00:01:40,100
I need to include that in its own brackets.

25
00:01:40,190 --> 00:01:46,960
If I take away the brackets let's just see what number we get. We get the wrong number.

26
00:01:46,970 --> 00:01:51,980
Excel also thinks we want to get a percentage returned so let's just change that to General.

27
00:01:51,980 --> 00:01:53,060
We get 59.

28
00:01:53,660 --> 00:01:53,900
Right?

29
00:01:53,930 --> 00:01:59,050
So putting the brackets in the correct place is really important.

30
00:01:59,060 --> 00:02:04,520
You don't always need it but just put the brackets because it's clear for everyone else what's being

31
00:02:04,520 --> 00:02:06,110
calculated first.

32
00:02:06,110 --> 00:02:11,110
So now let's highlight this, double click on the side, and send this down.

33
00:02:11,110 --> 00:02:17,330
Now another thing to keep an eye on is if your formulas are different within a range.

34
00:02:17,330 --> 00:02:22,840
Let's say by mistake someone comes in here and adds a plus two and we press enter.

35
00:02:22,970 --> 00:02:26,570
We get this green icon on the side of the cell

36
00:02:26,570 --> 00:02:31,540
and if you click on this exclamation mark, you see it says inconsistent formula.

37
00:02:31,600 --> 00:02:35,120
Now we can decide if we want to copy the formula from above

38
00:02:35,120 --> 00:02:38,820
and if you don't want to see this green icon you just want to hide it.

39
00:02:38,870 --> 00:02:41,480
You can go to error checking options.

40
00:02:41,480 --> 00:02:48,530
This takes you to the Excel options. And here under error checking, in the formula section, take away

41
00:02:48,560 --> 00:02:51,680
the tick mark for enable background error checking.

42
00:02:51,710 --> 00:02:58,610
This doesn't put that green icon on the side of the cell in case you have inconsistent formulas. But at

43
00:02:58,610 --> 00:03:02,200
the beginning when you're using Excel it's good practice to have it on.

44
00:03:02,300 --> 00:03:07,630
And once you're more confident about writing formulas then you can take away this checkmark.

45
00:03:07,640 --> 00:03:14,720
In this case let's just go and copy our formula all the way down. In the next example,

46
00:03:14,720 --> 00:03:18,890
we have our profit information on a year to date basis.

47
00:03:18,890 --> 00:03:26,390
This means March data for example includes January monthly, February monthly, and March monthly.

48
00:03:26,390 --> 00:03:33,130
Now we've been given the task to get the profit information per month instead of having it year to date.

49
00:03:33,140 --> 00:03:39,080
So what type of formula can we write to get the job done? Where for January

50
00:03:39,080 --> 00:03:45,060
Year to date and monthly is going to be the same value so we can just reference the first cell here,

51
00:03:45,080 --> 00:03:47,810
press enter and send the formula down.

52
00:03:48,200 --> 00:03:57,440
But what about February, March, April, and so on? February monthly would be the February year to date minus

53
00:03:57,530 --> 00:04:03,510
the January year to date which would be the January month. Press enter and send this down.

54
00:04:03,530 --> 00:04:05,260
Now what about March?

55
00:04:05,300 --> 00:04:06,890
Is it the same formula?

56
00:04:08,510 --> 00:04:11,300
It's similar to the formula I just wrote, right?

57
00:04:11,450 --> 00:04:16,880
Because March monthly would be the March year to date minus February year to date.

58
00:04:16,880 --> 00:04:23,580
This means that I can actually drag my formula all the way across and all the way down.

59
00:04:23,630 --> 00:04:27,350
The only formula that's completely different would be the January month.

60
00:04:27,800 --> 00:04:36,680
So if I drag this across and drag it down I get my formulas calculating correctly because they're all

61
00:04:37,010 --> 00:04:39,120
relatively referencing the cell.

62
00:04:39,230 --> 00:04:42,740
So when I click here I see it's July minus June.

63
00:04:42,800 --> 00:04:46,860
Let's go to the bottom corner here and see what we get.

64
00:04:46,880 --> 00:04:50,950
We get December minus November for the last item.

65
00:04:51,110 --> 00:04:53,610
So everything has relatively shifted.

66
00:04:53,800 --> 00:04:54,260
.

67
00:04:54,350 --> 00:04:57,050
The only thing is that January is different.

68
00:04:57,050 --> 00:05:03,920
And usually when you're writing formulas best practice is try to get the same formula for the entire range.

69
00:05:04,250 --> 00:05:06,070
For us to do that here,

70
00:05:06,110 --> 00:05:12,570
One option would be to use the if function which is one of Excels most useful functions.

71
00:05:12,710 --> 00:05:18,020
And that's something we're going to cover in the next section when we take a look at Excel functions.