# Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

1. ## Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

I have a range with about 1000+ items.

Some of them have values of zero

Also, some of the values are very low or very high.

So, I need a formula to remove highest 1/3 of values and lowest 1/3 of NON-ZERO values and get average of the remaining. (Column H on the attached spreadsheet has some zero values. So, I want to have 120 values which are non-zeros. I need something like
Formula:
`Please Login or Register  to view this content.`

2. ## Re: Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

Post a sample sheet

3. ## Re: Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

Pepe Le Mokko

4. ## Re: Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

Perhaps the following will help.
Cell J1 finds the count of non blank cells in column H using: =COUNT(H2:H155)
Cells I2 and down rank the non blank cells in column H using: =IF(H2="","",RANK.EQ(H2,H\$2:H\$155,0))
Cells J2 and down get the value of the middle 1/3 of the non blank cells in column H using: =IF(OR(I2="",I2<=40,I2>=80),"",H2)
Cell M1 displays the average of the values in column J using: =AVERAGE(J2:J155)
Let us know if you have any questions.

5. ## Re: Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

Thanks JeteMc

I appreciate the help.

7. ## Re: Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

I use different method

=AVERAGEIFS(H3:H155,H3:H155,">"&PERCENTILE.INC(H3:H155,1/3),H3:H155,"<="&PERCENTILE.INC(H3:H155,2/3))
or
=AVERAGE(INDEX(SMALL(H3:H155,ROW(INDEX(H:H,COUNT(H3:H155)/3+1):INDEX(H:H,COUNT(H3:H155)*2/3))),))

8. ## Re: Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values

You're Welcome and thank you for the feedback. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1