Locked lesson.
About this lesson
Learn basic statistical functions including COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN and MODE.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Basic Statistics.xlsx10.9 KB Basic Statistics - Completed.xlsx
11.3 KB Basic Statistics - Extra Practice.xlsx
16.4 KB
Quick reference
Topic
Basic statistical functions including COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN and MODE.
When to use
Determining basic statistics about the values that are in a range of data.
Instructions
COUNT vs COUNTA
|
=COUNT(A1:A10) |
Counts all numerical entries from A1 through A10. (Ignores text). |
|
=COUNTA(A1:A10) |
Counts all entries (including text) from A1 through A10. |
HIGH vs LOW values
|
=MAX(A1:A10) |
Returns the highest value in the provided range. |
|
=MIN(A1:A10) |
Returns the lowest value in the provided range. |
AVERAGES
|
=AVERAGE(A1:A10) |
Returns the mathematical average (mean) for the provided range. |
|
=MEDIAN(A1:A10) |
Returns the value of the number in the middle of your sorted data (or the value halfway between the two middle values if an even number of values is provided). |
|
=MODE(A1:A10) |
Returns the most frequently occurring value in your data set, or #N/A if no value occurs more frequently than any other. |
Note: In addition to the ones covered here, there are many other statistical functions available in Excel.
Login to download- 00:04 In this example we're looking at some data from U.S. hospitals on the cost of treatment and how profitable they are to the actual
- 00:09 hospital in question. We are going to run some basic statistics on this information to see
- 00:13 what it can actually tell us here and see how some of these functions work.
- 00:17 We are going to start with a COUNT function and say =COUNT and I'm going to count the number of records in the treatment cost column
- 00:24 and when I close my bracket you can see that it comes back to 12.
- 00:29 While I'm here I'm also going to do a COUNTA on the same column of data and
- 00:34 see what happens in this, these functions are very related. So they give me 12 records each. Now I'm going to copy these across and
- 00:41 you'll see that it'll actually start because it's relative to move across and actually
- 00:45 pick up the other columns and you can see what we have here.
- 00:48 You can see that our negotiated discount has a count of 11. Well there's 12 records here and
- 00:53 there's 12 records here.The COUNT gives us 11 COUNTA gives us 12 and
- 00:57 the reason being is because nil is actual text value and text values aren't picked up in COUNT nor blanks as you can see here.
- 01:06 Blank doesn't come up in either COUNTA or COUNT. Zeros do come up in COUNT though as we can see with 12.
- 01:12 Now lets look at AVERAGE
- 01:14 = AVERAGE go pull that on the treatment cost
- 01:18 and this will give us the numerical average that you would expect.
- 01:22 We can pull in MAX and pull our maximum values out as well.
- 01:29 We can pull a minimum value =MIN will give us our minimum for a range of data.
- 01:38 We can get a MEDIAN
- 01:40 and the median is the value that's the halfway point of your dataset so that will say that the numbers that are to the left will be lower
- 01:48 and to the right will be higher and that gives you the actuals so it's different than the actual average, a different statistical type.
- 01:54 And the MODE gives us the most frequently occurring value in this data set.
- 01:59 So in this particular case we can see 22,000 occurs here and here so it occurs twice and there is no other value that occurs twice or more
- 02:08 And again we can actually go and copy all this information across and take a look at what this actually
- 02:13 returns for us. So our averages return quite nicely across the board for us.
- 02:19 We can see our maximums so there's 534,000 in here for heart transplant.
- 02:23 There's 320,000 for the negotiated discount on it and the profitability is 176,000.
- 02:30 The minimum we have in this column is -13,250 which is right here for the kidney transplants. It looks like the minimum in our
- 02:37 customer discount column $7700 looks like we only give discounts or we don't give very big discounts on psychosis. And what do we have
- 02:45 in the median? 27,000 in this particular area, again the median is the halfway point between these pieces of information
- 02:53 so it's kind of tough to see these ones but here it is a 27,000 is a middle value.
- 02:58 In this case 3950 you won't see but if we were to take these particular values here
- 03:04 and copy this
- 03:05 we'll just paste it over here as some values
- 03:09 and sort it
- 03:12 and we'll just continue here.
- 03:16 You will see that the middle value 123456
- 03:20 the line breaks right here between 2400 and 5500. And if you actually take the difference between these two and add it to the 2400
- 03:29 you will find that it works out to 3950 so it's the value right set in the middle between your
- 03:35 that separates your high and low value number. Statisticians love these kind of things.
- 03:41 For most of us we don't generally tend to work a lot with median but there you have it if you need it.
- 03:46 And then the mode you can see that zero is the most frequently occurring value here one thing that's interesting is that if you do go and delete
- 03:52 this and there is no mode, no frequently occurring value everything occurs with the same frequency
- 03:56 the MODE will give you a N/A value.
- 04:00 So that's how we work with basic statistics functions inside Excel.
Lesson notes are only available for subscribers.