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]

I have posted my spreadhseet and output that I require.

I am not denying that Trevor's works: But it would mean my output would be really comlicated. I would need Count, Av, Stdev for every combination of position and level of play.

 

[Tinus Bergakker]

Why not modify it.

Here is what I have currently assuming the spreadsheet starts in Range A1

COUNT : {=SUM(IF(B1:B47="Prop",IF(D1:D47 > 0,1,0),0))}
AVERAGE : {=SUM(IF(B2:B48="Prop",IF(D2:D48 > 0,D2:D48,0),0)) / COUNT }

Now for the last part, the STDEV I added a column E that does:

=(D2-VLOOKUP(B2,$G$3:$I$9,3,FALSE))^2 with the table being your yo-yo table.

{=SUM(IF(B1:B47="Prop",IF(D1:D47 > 0,1,0),0))}

The STDEV column finally being

=SQRT(SUM(IF(B1:B47="Prop",IF(D1:D47 > 0,E1:E47,0),0)) / VLOOKUP(B2,$G$3:$I$9,2,FALSE))

 

[Tinus Bergakker]

For the STDEV, this works better

=STDEV(IF(B2:B16="Prop",IF(D2:D16>0,D2:D16,""),""))

This is similiar to Trevor's formula. Sorry the previous one was so complicated.

 

[georgepet]

The count and average formulas bring up either a '0' or reference error repsectivley.

The STdev formula confuses me.

I do not want to change my output as my reports read directly from it.

I am pretty sure the IF function will not work It has to use an array forula with SUBTOTAL and OFFSET.

 

[Tinus Bergakker]

Press ctrl+shift+enter when entering the formula

 

[georgepet]

Yes I did that

 

[Tinus Bergakker]

Ok. Well I know the IF function with the STDEV function works. I tested it using your data.

Assuming you have corrected the references, this is what

=STDEV(IF(B2:B16="Prop",IF(D2:D16>0,D2:D16,""),"")) does

Is the string in the position column = "Prop" ?

If so, make an array where blank values are ignored. In this case I used > 0.

Apply the STDEV function on this final array.

 

[georgepet]

Yes but does it give the correct value when you filter just Level 1 etc?

 

[Tinus Bergakker]

Sorry no,

for level you can modify it so that

=STDEV(IF(C2:C16=1,IF(D2:D16>0,D2:D16,""),""))

So you would have 2 formulas one for level only, another for position.

If I am mistaken and you wanted the combination, Trevor has given that formula.

 

[georgepet]

Yes Trevor provided a combination formula but I would need a output cell for every combination possible (4 levels, 8 positions). This is not practical.

I really appreciate your help. I would not be this far without help from Excel experts like yourself.