1
00:00:00,180 --> 00:00:04,030
It's pretty cool how much information you
can quickly calculate using a spreadsheet.

2
00:00:04,030 --> 00:00:07,310
Previous generations had to spend a lot
more time to answer questions that

3
00:00:07,310 --> 00:00:09,850
spreadsheets let us answer
in less than a second.

4
00:00:09,850 --> 00:00:13,570
In this example spreadsheet, we're looking
at a lot of different retail transactions.

5
00:00:13,570 --> 00:00:16,120
And say we want to understand,
what the average price of the items

6
00:00:16,120 --> 00:00:17,430
we are selling is.

7
00:00:17,430 --> 00:00:20,100
You might do this to figure out if
you have opportunities to change your

8
00:00:20,100 --> 00:00:22,220
pricing strategies, for example.

9
00:00:22,220 --> 00:00:26,260
To calculate the average price, we can use
the average function which is similar to

10
00:00:26,260 --> 00:00:29,380
the sum function we introduced
in an earlier video.

11
00:00:29,380 --> 00:00:33,858
In the average case, the syntax is
very simple and similar to sum.

12
00:00:33,858 --> 00:00:38,360
Let's scroll down to where
we had the sum function.

13
00:00:40,520 --> 00:00:45,440
And Instead of sum,
we're gonna type out AVERAGE.

14
00:00:47,690 --> 00:00:52,048
Then parentheticals, and then select
all the values that we wanna sum.

15
00:00:57,064 --> 00:01:03,560
So the average price transaction for
this data set is $289.71.

16
00:01:03,560 --> 00:01:07,750
The median function is basically
the exact same as the average function,

17
00:01:07,750 --> 00:01:10,950
except we're gonna be typing
median instead of average.

18
00:01:10,950 --> 00:01:12,570
Before using the median function,

19
00:01:12,570 --> 00:01:15,240
let's note why sometimes
an average is misleading.

20
00:01:15,240 --> 00:01:18,533
Let's look at another data
set to illustrate this.

21
00:01:18,533 --> 00:01:20,866
We go over to the average median tab.

22
00:01:20,866 --> 00:01:26,660
And this tab, we're looking at the total
sales by month for a full year.

23
00:01:26,660 --> 00:01:31,758
So, in January we had
$500,000 worth of sales.

24
00:01:31,758 --> 00:01:36,433
In August, $100,000 worth of sales,
September at $90,000 worth of sales,

25
00:01:36,433 --> 00:01:38,040
so forth and so on.

26
00:01:38,040 --> 00:01:40,720
Let's say your boss asks you to
calculate what the average monthly

27
00:01:40,720 --> 00:01:42,370
sales were for the year.

28
00:01:42,370 --> 00:01:45,520
January sales were much more
than other months though.

29
00:01:45,520 --> 00:01:47,960
In this case,
the average might skew high and

30
00:01:47,960 --> 00:01:50,290
not reflect the sales
of most of the months.

31
00:01:50,290 --> 00:01:52,020
The median is a better choice,

32
00:01:52,020 --> 00:01:55,680
as it helps eliminate outline
values that are extremely or low.

33
00:01:55,680 --> 00:01:59,340
In this situation, you might answer your
boss's question about what the average is,

34
00:01:59,340 --> 00:02:01,690
and then also point out that
you included median as well,

35
00:02:01,690 --> 00:02:05,080
because it may be a better indicator for
their underlying question.

36
00:02:05,080 --> 00:02:07,720
Another option is to note that
January was an outlier month, and

37
00:02:07,720 --> 00:02:09,880
just remove that from your data set.

38
00:02:09,880 --> 00:02:14,380
So here we've calculated the average for
all the different calendar months.

39
00:02:15,850 --> 00:02:18,057
And then below it,
we've calculated the median.

40
00:02:20,267 --> 00:02:24,190
The average is $135,833 and
the median is $100,000.

41
00:02:24,190 --> 00:02:26,570
So that's a significant difference.

42
00:02:26,570 --> 00:02:30,960
What about the average is we remove
the outlier month, as we've done here?

43
00:02:30,960 --> 00:02:32,190
We removed January.

44
00:02:33,830 --> 00:02:38,100
And now the average is $102,727,
much closer to the median for

45
00:02:38,100 --> 00:02:40,480
the entire data set.

46
00:02:40,480 --> 00:02:44,961
Note that when I select a group of cells,
I can get a handy snapshot of

47
00:02:44,961 --> 00:02:48,672
a bunch of functions without
having to write them down.

48
00:02:48,672 --> 00:02:50,630
You can see it here in
the bottom right of the screen.

49
00:02:52,610 --> 00:02:58,570
We can see Sum, Average, Min,
Max, Count, and Count Numbers.

50
00:02:58,570 --> 00:03:00,440
Just something to keep in mind.

51
00:03:00,440 --> 00:03:03,880
The sum is the total,
the average is the average for

52
00:03:03,880 --> 00:03:04,910
the cells that you've selected.

53
00:03:04,910 --> 00:03:08,100
The min is the minimum value in
the group of cells that you've selected.

54
00:03:08,100 --> 00:03:10,860
The maximum is the maximum

55
00:03:10,860 --> 00:03:13,340
value in the group of
cells you've selected.

56
00:03:13,340 --> 00:03:16,830
Count is the number of
cells you've selected, and

57
00:03:16,830 --> 00:03:19,530
count numbers will only calculate numbers.

58
00:03:19,530 --> 00:03:26,780
So if I go here and select all these and
come back, you can see that count,

59
00:03:26,780 --> 00:03:30,730
there's 14 cells that I've selected, but
only 7 of them contain numerical values.

60
00:03:32,240 --> 00:03:34,650
Now that you started using some functions,

61
00:03:34,650 --> 00:03:38,330
let's see what else we can do with this
simple example to gain further insights.