What happens very often is we want to figure out an average averages, help us tell the story of a large set of data, but averages also can lie.

Explore ↓

Better Sheets

Tools, Templates, & Tutorials

Explore ↓

Free Resources ↓

Sign up to get access.

About this Tutorial

What happens very often is we want to figure out an average averages, help us tell the story of a large set of data, but averages also can lie.

Sheet Resources

Video Transcript

Hey, sheet shakers. This video is not necessarily about how data is displayed, but how we get the data. So sort of a number crunching data, crunching data among kind of video. What happens very often is we want to figure out an average averages, help us tell the story of a large set of data, but averages also can lie.

I have had really interesting experiences with averages and found this one practice of using minimum maximum to take away outliers to greatly help me when trying to describe or tell a story of some numbers. This could be useful Here. I'm doing views, like a video views or it's also useful for revenue numbers if you have like an outlier month, if you know that, you know, oh, the quarter 2018 we had.

Record setting sales. But you know, we wanna find the average lifetime each quarter. Benchmarking, we use it for indexing. You so many reasons and ways you may wanna find out a real average instead of just the average. If you do basically you wanna get rid of the outliers, that's essentially what we're doing.

And we use minimum and maximum to that very efficiently, very quickly. And you can do this in a number of ways, but I'm gonna show you just here with views. Again, you can use this for sales numbers, revenue numbers customer churn. You know, if you wanna find out what is the not percentage of, of people churning from your business, but you just wanna know the actual number and say, okay, that's sort of our benchmark every month.

But you find out, you know, oh, we had you know, 12 months ago we had a problem with some sign. Function or function inside the, the app and we had large churn, we want to kill that. And, and sometimes you might have to go into the numbers and literally just delete 'em. But we can also figure out do this programmatically, quote unquote or automatically with using minimum maximum.

So here's what we did. Or here's what I did have his views column and I'm getting the average of it. . It says average before to B so that I don't have to keep increasing that every time I add some number here, it'll change this average, which is cool. So we'll have a rolling average. Here's the median.

It is significantly smaller. And you know, this might happen. Averages and medians are very different. You might have a variety of reasons why your median is bigger. smaller than your average, but in this case, our average is 42% higher than our median. And maybe we didn't have any growth in these views, but like maybe that video, one of our videos went really, really well and we can never replicate that number.

So we really wanna just take that away. What we did instead of average. I actually broke it up into basically a sum is the ar, the sum, the average is the sum of all the numbers divided by the amount of them. So in this one we did that, we said some, and then actually let's take out the minimum maximum for now.

And so here's the sum, same number as above of.

So there you can see just taking the sum of B four to B and dividing it by count all count A of B, four to B. And so literally all we're going to do here is instead of taking the count, some of the count, all.

We will go minus m i n and we'll do B four to B. Then we're also gonna subtract the maximum number, so that's just m a x max, B four to B. We're gonna put that all in in parenthesis here. And now the count all is gonna be wrong or off, so it's gonna be still dividing by the total amount. So we actually just want to go over to the count all.

and we want end minus 2, 4, 2, less than count, count all. Put that in parentheses as well. And then end parentheses. So again, this is just taking the sum the exact same thing we did before some. Divided by the count, but taking out minimum and maximum the absolute highest amount and the absolute lowest amount and then averaging those, which basically is the count minus two.

And this gives us a much better average. . Yeah. So our, we have an average of 461,000, which is now only 24% higher than the median. Which is within a re, like you can tell that story. You can say, okay, here's our average. These numbers are trailing upwards. Maybe because we're doing growth, we're having some, you know, our total subscriber count is going up, or the distribution points, we have more distribution points.

You can then tell that story much. Then, oh my God, our average is 42% higher than our median. This again, lots of really cool use cases for this. In revenue count sales anytime, you know, there's an outlier, you can see an outlier on charts, you can see an outlier in a, in a list here. I would recommend not using this every time for your average.

an average of a list of numbers is, should be accurate. It's not great to use for other purposes, but if this is just really useful when you see an outlier and you don't necessarily want to delete this data or have to manually take out minimum a maximum. This also could. replicated many, many times and you can have a running average as well.

Really, really cool use case for this for using minimum and maximum, I think. Thanks. Bye.

I have had really interesting experiences with averages and found this one practice of using minimum maximum to take away outliers to greatly help me when trying to describe or tell a story of some numbers. This could be useful Here. I'm doing views, like a video views or it's also useful for revenue numbers if you have like an outlier month, if you know that, you know, oh, the quarter 2018 we had.

Record setting sales. But you know, we wanna find the average lifetime each quarter. Benchmarking, we use it for indexing. You so many reasons and ways you may wanna find out a real average instead of just the average. If you do basically you wanna get rid of the outliers, that's essentially what we're doing.

And we use minimum and maximum to that very efficiently, very quickly. And you can do this in a number of ways, but I'm gonna show you just here with views. Again, you can use this for sales numbers, revenue numbers customer churn. You know, if you wanna find out what is the not percentage of, of people churning from your business, but you just wanna know the actual number and say, okay, that's sort of our benchmark every month.

But you find out, you know, oh, we had you know, 12 months ago we had a problem with some sign. Function or function inside the, the app and we had large churn, we want to kill that. And, and sometimes you might have to go into the numbers and literally just delete 'em. But we can also figure out do this programmatically, quote unquote or automatically with using minimum maximum.

So here's what we did. Or here's what I did have his views column and I'm getting the average of it. . It says average before to B so that I don't have to keep increasing that every time I add some number here, it'll change this average, which is cool. So we'll have a rolling average. Here's the median.

It is significantly smaller. And you know, this might happen. Averages and medians are very different. You might have a variety of reasons why your median is bigger. smaller than your average, but in this case, our average is 42% higher than our median. And maybe we didn't have any growth in these views, but like maybe that video, one of our videos went really, really well and we can never replicate that number.

So we really wanna just take that away. What we did instead of average. I actually broke it up into basically a sum is the ar, the sum, the average is the sum of all the numbers divided by the amount of them. So in this one we did that, we said some, and then actually let's take out the minimum maximum for now.

And so here's the sum, same number as above of.

So there you can see just taking the sum of B four to B and dividing it by count all count A of B, four to B. And so literally all we're going to do here is instead of taking the count, some of the count, all.

We will go minus m i n and we'll do B four to B. Then we're also gonna subtract the maximum number, so that's just m a x max, B four to B. We're gonna put that all in in parenthesis here. And now the count all is gonna be wrong or off, so it's gonna be still dividing by the total amount. So we actually just want to go over to the count all.

and we want end minus 2, 4, 2, less than count, count all. Put that in parentheses as well. And then end parentheses. So again, this is just taking the sum the exact same thing we did before some. Divided by the count, but taking out minimum and maximum the absolute highest amount and the absolute lowest amount and then averaging those, which basically is the count minus two.

And this gives us a much better average. . Yeah. So our, we have an average of 461,000, which is now only 24% higher than the median. Which is within a re, like you can tell that story. You can say, okay, here's our average. These numbers are trailing upwards. Maybe because we're doing growth, we're having some, you know, our total subscriber count is going up, or the distribution points, we have more distribution points.

You can then tell that story much. Then, oh my God, our average is 42% higher than our median. This again, lots of really cool use cases for this. In revenue count sales anytime, you know, there's an outlier, you can see an outlier on charts, you can see an outlier in a, in a list here. I would recommend not using this every time for your average.

an average of a list of numbers is, should be accurate. It's not great to use for other purposes, but if this is just really useful when you see an outlier and you don't necessarily want to delete this data or have to manually take out minimum a maximum. This also could. replicated many, many times and you can have a running average as well.

Really, really cool use case for this for using minimum and maximum, I think. Thanks. Bye.

Better Sheets

Something for everyone to Learn Google Sheets. Includes Google Sheets for Beginners and Advanced Google Sheets Tutorials alike.

Google Sheets Help

Google Sheets Courses

Google Sheet Templates and Tools

Google Sheet Add Ons