How to average with and without the highest and lowest values in Excel

Calculating averages in Microsoft Excel is easy, until you start to rule out certain values. Here are three ways to average a data set when giving special consideration to higher and lower values.

Microsoft Excel on screen

Photo: Wachiwit / Shutterstock

The article reviews how to do the most advanced averaging in Excel’s many averaging functions in Microsoft Excel. Emphasis is placed on how functions evaluate zero, booleans, and null string values. However, these functions will not always be sufficient. In fact, sometimes you’ll need an expression that doesn’t use any average functions at all. In this article, we’ll look at some unique averaging solutions.

We see: 69 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use earlier versions. You can download the .xlsx demo file and . xls , or work with your own data. The browser supports all functions.

Quick Review of Calculating Average in Excel

Just in case you are not familiar with the AVERAGE() function, we will start with a quick review of this function. As you might expect, this function adds the reference values ​​and then divides that total by the number of values. You can reference cells, range, and even literals. It is important to remember how this function deals with non-traditional values:

  • Text ignored
  • Booleans or Booleans TRUE and FALSE are ignored.
  • Blank cells are ignored.
  • If the function references an error value, it returns an error.

as you see in Figure A, AVERAGE() returns the average value of a simple data set. The resulting mean is the same as if you added and divided it: =SUM(B4:B8)/5. We’ll continue working with this dataset while we complicate things a bit.

Figure A

excelaveragemore-a.jpg

Use AVERAGE() to return the mean of a data set.

How to Calculate Only Average High and Low in Excel

Calculating the average of the highest and lowest values ​​in a data set seems to be a vague requirement, but I’m including it so you can see how AVERAGE() can work with other functions. For this solution we will sum AVERAGE(), MAX(), and MIN(). Figure B Show job results

= AVERAGE (MAX (B4: B8), MIN (B4: B8))

and returns the value 32.75, the same as the expression (1.5 + 64)/2.

Figure B

excelaveragemore-b.jpg

This function excludes the highest and lowest value in the data set when calculating the mean.

It’s easy to determine how this function works, but let’s evaluate it using the sample dataset:

= AVERAGE (MAX (B4: B8), MIN (B4: B8))

= average (64,1.5)

32.75

This function is simple, but sometimes you will not want to delete certain values, but rather delete a percentage of the values. This is what we will take up next.

How to get a clipped mean in Excel

You probably know that this means average. For the most part, the only difference is the term: mathematicians use the mean and statisticians use the mean. However, not every means is an average because there are different types of means. However, the term arithmetic mean and mean are synonymous.

We see: Windows 10: Voice Command Menus for Speech Recognition and Dictation (Free PDF) (TechRepublic)

With this discussion to fill in some gaps, it’s time to introduce the TRIMMEAN() function in Excel, which removes the given percentage from the largest and smallest values, known as the cut-off. This happens when the outliers are outliers and distort the results – by removing the values, the mean is a little more realistic.

TRIMMEAN() removes a percentage from both ends, so sometimes it only ends up being the largest and smallest; Sometimes you will remove two or more values ​​from both ends. It’s unlikely that an average user would ever need this functionality, but I’m including it to be comprehensive.

This function uses the form

TRIMMEAN (elegant – tidyAndpercentage)

Where the range indicates the values ​​evaluated and the percentage indicates the number of values ​​to be removed from both ends. You can enter the percentage using one of two formats: 0.2 or 20%. Our small dataset returns the same value for both AVERAGE() and TRIMMEAN(), as shown in Figure C. In this case, the 0.2 percentage returns one value that must be removed (5 values ​​divided by 20% equals 1). This value is split between the two extremes, the highest and the lowest, and of course you can’t remove half a value, so don’t remove any of it. Both results will remain the same until you change the percentage to 40% (0.4).

Figure C

excelaveragemore-c.jpg

TRIMMEAN() can return an unexpected result.

It’s a bit unfortunate because TRIMMEAN() doesn’t give a more realistic result with our dataset, as you just saw. Let’s take a look at the expression that will.

How to Exclude High and Low in Average in Excel

It is common to exclude the highest and lowest values, or outliers, when calculating the mean. Educators and statisticians do this frequently. What we do is similar to the function in the previous section, but TRIMMEAN() removes a percentage; In this example, we want to remove only the highest and lowest value, not the percentage of values.

Although the expression appears in figure d

= (SUM (B4: B8) – (SUM (MAX (B4: B8)) + MIN (B4: B8)))) / (COUNT (B4: B8) -2)

long, not complicated; It contains a lot of parts. In short, it returns the same thing as SUM(3,10,4)/3. The evaluated values ​​exclude 1.5 and 64.

figure d

excelaveragemore-d.jpg

This mean does not include the highest and lowest value in the data set.

Let’s evaluate this expression using our dataset:

= (SUM (B4: B8) – (SUM (MAX (B4: B8)) + MIN (B4: B8)))) / (COUNT (B4: B8) -2)

= (82.5) – (64 + 1.5) / (5-2)

= 17/3

5.666666667

I’m not convinced that there is no shorter expression, but the logic on this one works. From the sum of the entire data set, you can subtract the sum of the two outliers (64 + 1.5). Then you divide that total, 17, by 3, because there are only three values ​​that are evaluated. You will always subtract 2 from the total number of values ​​because you are only removing 2 values ​​from the data set.

These intermediate solutions are severe; You will not always have such strict restrictions. When you do, with a little know-how, you can get the job done.

see also