Subtotal STDev - With Criteria

[georgepet]

Hi,

I have a flat table with the auto filter function. In column D2:D1000 I have 'Position' (e.g. Prop, Hooker, Scrum Half). In column G2:G1000 I have a score (e.g. 15.0, 15.7).

In my subtotal table, I wish to have results that represent the scores of each position. The usual =SUBTOTAL function does not allow this as it uses all the positional data regardless of filtering (I only wish to ever filter one other column, Level of Player).

I have managed to work out how to find the number of players:

=SUMPRODUCT(SUBTOTAL(2,OFFSET('January 2010'!$G$2:$G$1000,ROW('January 2010'!$G$2:$G$1000)-MIN(ROW('January 2010'!$G$2:$G$1000)),,1)),--('January 2010'!$D$2:$D$1000="Prop"))

And the average of the scores:

=SUMPRODUCT(SUBTOTAL(3,OFFSET('January 2010'!$D$2,ROW('January 2010'!$D$2:$D$1000)-ROW('January 2010'!$D$2),,1)),--('January 2010'!$D$2:$D$1000="Prop"),'January 2010'!$G$2:$G$1000)/SUMPRODUCT(SUBTOTAL(3,OFFSET('January 2010'!$G$2,ROW('January 2010'!$D$2:$D$1000)-ROW('January 2010'!$D$2),,1)),--('January 2010'!$D$2:$D$1000="Prop"))

But now I wish to find the standard deviation of this average!

I hope by pasting my previous formulas, some brightspark may be able to help me?

Thank you for reading this anyway!

 

[georgepet]

You are amazing!

I cannot understand the reason for your persistence with this but it is truly incredible and truly appreciated. Thank you so much. I hope you have not wasted any time doing proper things!!!

Thank you so much again Tinus.

 

[Tinus Bergakker]

I hate not being able to do something in excel. I learnt alot through this process as well. What I have showed you is a new trick I would not have learnt without your query.

So you're welcome and thanks from me too.

 

[georgepet]

Tinus, I have one final challenge for you.

I have tried but failed...

I am attempting to find the top 10% score boundary in each position every time I filter. For example, If I have 50 players in the Prop position, I am looking for the score of the player ranked 5th best.

I would normally calculate it this way:

=LARGE(range,(COUNT(range)*0.1))

I am having difficulty fitting this into my final table. Do you have any ideas?

 

[Tinus Bergakker]

How about:

=LARGE(IF(E2:E48=1,D2:D48,""),COUNT(IF(E2:E48=1,D2:D48,""))*0.1)

D column is scores, E column is SUBTOTAL(102,..) formula