# Average the bottom 95\$ of values

1. ## Average the bottom 95\$ of values

Hi, I am looking to create an average or trimmean formula that allows me to average the bottom 95% of my values. The values being turnaround time in days for often greater than 500 items.

The number of values I look at from month to month changes but i want the formula to ignore the top 5% while generating the average and be able to account for the change in item count.

To add to this I also need it to only calculate the average for those values only when it meets a criteria of "Y".

I should also mention I would like to accomplish this without using a Pivot.

Can anyone help with this or is this too complex for excel.

2. ## Re: Average the bottom 95\$ of values

Hi, welcome to the forum

Sounds fairly simple, I would use =AVERAGEIFS() function

=AVERAGEIFS(data-range,Data-Range,"<="cell-with-95%-value-in-it,Criteria-Range,"Y")

3. ## Re: Average the bottom 95\$ of values

In case you dont already have it, you can get the 95% value withthis...
=PERCENTILE.EXC(B2:B25,0.95)

4. ## Re: Average the bottom 95\$ of values

Hi, thanks for the suggestions so far.

I do think I need to clarify that the values I'm trying to calculate on are not percentage values. They are simple numeric values of which I may have, for example, 500 of and what I am looking to do is generate an average ignoring the top 5% largest values out of the 500 in the list. So essentially I would want to ignore the 25 largest values out of the 500.

If I understood your suggestions correctly I believe I did not make this too clear.

Let me know if this changes anything.

5. ## Re: Average the bottom 95\$ of values

So you are looking at the top 95% by quantity, not by value?

6. ## Re: Average the bottom 95\$ of values

Sorry, replied a bit too quickly. Yes and no. I want the bottom 95% by quantity but by bottom 95% also includes value of the items being included.

10
9
25
8
3
5
5
10
2
6

I would for example want to leave off the largest value of 25 being the top 5% i would ignore and get the average of the remaining bottom 95%.

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