Average Top 90% Help

[skate1991]

Hi Guys,

I am looking to average the top X percent and have managed to build a formula which does this which is in cell D1 on the example worksheet.

What I haven’t been able to manage to do and hope one of you guys can help with is to build two criteria into this. The formula is built to calculate the bottom 90% of values.

The two criteria are in col A & B

In col G:I i have put a little summary table with an answer I would expect to get based on this data for client 1 standard.

Thanks for your help.

[eibi]

I think your sample answer may be a red herring?

I have calculated it a few times and keep getting 3.4 instead of 4.1 -- could there be an error in your manual calculations?

I do have a formula to offer, but the forum keeps blocking me from posting it as text...

code.png

This screenshot is the best I can do.

It's an array formula; Confirmed with Ctrl+Shift+Enter. Paste in H2 and drag to fill.

[FlameRetired]

I don't come up with the same figures either.

Perhaps I misinterpret.

Row\Col	G	H	I	J
1		Standard	Complex
2	Client 1	5.285714	4.789474	In H2 :=AVERAGEIF($A$1:$A$40,$G2,$C$1:$C$40)
3	Client 2	5.736842	5.235294	In I2 :=AVERAGEIFS($C$1:$C$40,$C$1:$C$40,"<="&PERCENTILE($C$1:$C$40,0.9),$A$1:$A$40,$G2)

[skate1991]

Hi Flameretired,


Your formula gets the results I was expecting but it doesnt seem to take into consideration the complex/standard field.

I have uploaded an example spread sheet. I get the same figure for complex and standard even know all the cases are set to standard.

[eibi]

If you want to add the Standard/Complex criteria to FR's formula, paste this in H2 and drag to fill:

=AVERAGEIFS($C$1:$C$40,$C$1:$C$40,"<="&PERCENTILE($C$1:$C$40,0.9),$A$1:$A$40,$G2,$B$1:$B$40,H$1)

[FlameRetired]

@ikeene

Yep. Thanks for the backup. I overlooked that.

[FDibbins]

This seems remarkably similar to another thread here...
http://www.excelforum.com/showthread...55#post4328655

If this is your thread too, (and both have similar IP's), I would ask that you stick with 1 ID.
If that was not your, my apologies, but you have to admit, they look the same

[skate1991]

Hi ikeene & FlameRetired

I had a look at the formula and when I applied it to a larger data set it doesn't seem to work.

I have attached an example. in H2 is the formula you provided and it shows 28.32 but when I work it out manually I get to 27.9

My workings in H6:H8, basically there is 2,307 records 90% of this would leave 2,076 records when I average the 2,076 records I get 27.90

Thanks for your time already on this.

FDibbins - its definitely not me, not sure how IP addresses work but could it be someone else in my office building?

Thanks

[eibi]

skate -- here's why your manual calculations produce a different outcome than the PERCENTILE() formula you requested in your original post:

In your manual calculations, you are averaging the bottom 90% of the records.

(which is 2076 of the total 2307 records)

In your PERCENTILE formula, you are averaging all the records that fall in the bottom 90th percentile.

(which includes all the records with a score of 47.00 or lower -- actually 2123 of the total records)

These two options are materially different -- and a statistician (I am not one) will probably tell you it's bad practice to take the average of a percentile...

[newdoverman]

Using your first example (I don't understand your second example)
Enter in H2 and fill down and across

=AVERAGEIFS((IF($C$1:$C$40>PERCENTILE($C$1:$C$40,0.1),$C$1:$C$40)),$A$1:$A$40,$G2,$B$1:$B$40,H$1)

Result:

	G	H	I
1		Standard	Complex
2	Client 1	4.636364	6
3	Client 2	5.444444	6

[skate1991]

Hi Everyone

thanks for all your input I dont know if I was clear in my original request but just to clear up my question. What I want to work out is the bottom 90% of turnaround times on cases.

The way I would want this working out would be if I was to find the bottom 90% and there was 100 cases then it would be the average of the 90 quickest cases. if it was 500 cases it would be the quickest 450 cases

I'm not 100% what percentiles are but this is the way I have been asked to work it out.

Again I just wanted to say thank you to everyone whose tried to help me find a solution to this.

Thanks

[newdoverman]

Maybe this will work for you

=AVERAGEIFS((IF($C$1:$C$2308<=PERCENTILE($C$1:$C$2308,0.9),$C$1:$C$2308)),$A$1:$A$2308,$G2,$B$1:$B$2308,H$1)

Percentile is somewhat like % but not the same. If you scored the 75th percentile on an exam, that would mean that for every 100 participants, you would be number 75 in the rankings or in other words only 25 people per 100 scored better than you. The actual scores are not averaged. It is the position of your scores relative to the others. If you scored 95th percentile only 5 per every 100 scored better.

[newdoverman]

This will average the smallest 90% of the values in column C.
Array enter this

=AVERAGE(IF(C1:INDIRECT("c"&COUNT(C:C))<PERCENTILE(C1:INDIRECT("c"&COUNT(C:C)),0.9),C1:INDIRECT("c"&COUNT(C:C))))

Maybe someone can chip in with the criteria....have to leave for now.

[eibi]

Skate --

In case you need to be able to explain (to your boss, for example) why the percentiles are not giving you same answer as the average of the bottom 90%, a brief explanation:

A percentile takes a snapshot of the score in the 90% position. In your data set provided in post #8, above, this score was 47.0, for example

However, there was more than one result of 47.0 -- in fact, that score appeared 57 times in the sample data you provided --

When you chop off the top 10%, and average the remaining 90%, you actually chop off some of the 47.0 scores, and you average the remaining 2,076 scores together...

BUT. When you average every score at-or-below the 90th percentile, you include all those results that were at or below 47.0 -- which means that you are averaging 2,123 scores together...

I sure hope this is a helpful explanation...maybe at least you can smile knowingly next time the boss can't make his statistics match up...