• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average best 95 % of list

cmpechirra

New Member
How do I calculate an average of the best 95 % (lowest values in this case) of some columns (column length is variable)?


Thanks in advance
 
Hi cmpechirra,


Welcome to the chandoo's forum. How Best 95% can be the lowest of any column?? Can you elaborate the problem a bit more.


Regards,

Faseeh
 
You could use this array formula:

=AVERAGE(IF(A1:A100<=PERCENTILE(A1:A100,0.95),A1:A100))


Array formulas must be confirmed using Ctrl+Shift+Enter.


Faseeh, I think the statement could be interpreted as meaning that a lower/smaller value is desired. E.g., find the best golf scores.
 
I did it without the array formula in Excel 2007:


=AVERAGEIF(A1:A100,"<"&PERCENTILE(A1:A100,0.95))
 
Hello guys ,


I have a problem ( still) . For some reason , the


=AVERAGEIF(A1:A100,"<"&PERCENTILE(A1:A100,0.95))

( with the adapted values to my sheet ) does not work .

I wonder whether the "<"&PERCENTILE (A2:A100)is the correct syntax , but Excel Help tends to be very litle helpful .


Help from you would be highly appreciated again :)
 
Hi, cmpechirra!


I've typed in a clean sheet in A1 =ROW() and copied down thru A100.

In B1 I copied the formula and it displays 48 which is right and is equal to what Excel shows at bottom page if I select A1:A95 cells range.

So I recommend you to re-check manually your data sorting in ascendent order, selecting first 95 rows (or equivalent if you have more or less) and check against what Excel displays at bottom page.

Otherwise consider uploading a sample file. Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Back
Top