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!

 

[Tinus Bergakker]

May I suggest an approach then

<table border="0" cellpadding="0" cellspacing="0" width="351"><col style="width: 48pt;" width="64" span="3"> <col style="width: 71pt;" width="95"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl63" style="height: 15.75pt; width: 48pt;" width="64" height="21">
</td> <td colspan="2" class="xl75" style="border-left: medium none; width: 96pt;" width="128">Yo-Yo score</td> <td class="xl65" style="width: 71pt;" width="95">Visible Level</td> <td class="xl65" style="width: 48pt;" align="right" width="64">1</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt; width: 48pt;" width="64" height="21">
</td> <td class="xl67" style="width: 48pt;" width="64">N</td> <td class="xl67" style="width: 48pt;" width="64">Mean</td> <td class="xl67" style="width: 71pt;" width="95">Std</td> <td class="xl68" style="width: 48pt;" width="64">Top 10%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt;" width="64" height="20">Prop</td> <td class="xl71" style="width: 48pt;" align="right" width="64">5</td> <td class="xl71" style="width: 48pt;" align="right" width="64">15.7</td> <td class="xl70" style="width: 71pt;" width="95">
</td> <td class="xl72" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt;" width="64" height="20">Hooker</td> <td class="xl71" style="width: 48pt;" align="right" width="64">1</td> <td class="xl71" style="width: 48pt;" align="right" width="64">17.8</td> <td class="xl70" style="width: 71pt;" width="95">
</td> <td class="xl72" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt;" width="64" height="20">Lock</td> <td class="xl71" style="width: 48pt;" align="right" width="64">3</td> <td class="xl71" style="width: 48pt;" align="right" width="64">16.4</td> <td class="xl70" style="width: 71pt;" width="95">
</td> <td class="xl72" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt;" width="64" height="20">Backrow</td> <td class="xl71" style="width: 48pt;" align="right" width="64">6</td> <td class="xl71" style="width: 48pt;" align="right" width="64">17.2</td> <td class="xl70" style="width: 71pt;" width="95">
</td> <td class="xl72" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt;" width="64" height="20">Half Back</td> <td class="xl71" style="width: 48pt;" align="right" width="64">2</td> <td class="xl71" style="width: 48pt;" align="right" width="64">18.8</td> <td class="xl70" style="width: 71pt;" width="95">
</td> <td class="xl72" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt;" width="64" height="20">Centre</td> <td class="xl71" style="width: 48pt;" align="right" width="64">2</td> <td class="xl71" style="width: 48pt;" align="right" width="64">18.4</td> <td class="xl70" style="width: 71pt;" width="95">
</td> <td class="xl72" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt;" width="64" height="20">Back 3</td> <td class="xl71" style="width: 48pt;" align="right" width="64">3</td> <td class="xl74" style="width: 48pt;" align="right" width="64">17.7</td> <td class="xl73" style="width: 71pt;" width="95">
</td> <td class="xl64" style="width: 48pt;" width="64">
</td> </tr> </tbody></table>

If you create the table as above, where visible level corresponds to the level you are filtering via a SUBTOTAL formula.

Then you can use trevor's formula with your table to create the wanted totals?

e.g.

{=STDEV(IF(B2:B18="Prop",IF(C2:C18=Visible Level,D2:D18)))}
{=STDEV(IF(B2:B18="Hooker",IF(C2:C18=Visible Level,D2:D18)))}

etc.

 

[Tinus Bergakker]

This would give the visible level:

=SUBTOTAL(1, Level column)

If this is not an integer, you can be reasonably certain that you are not filtering. Given the number of players is large enough.

 

[georgepet]

Again, an excellent idea but I have probably not explained the magnitude of the flat table to you.

Sometimes we will filter Level, sometimes Club, Sometimes weight, height, date, age etc.

In the case of your formula we would need a separate table for each variable.

Sorry but thank you for your help!

 

[Tinus Bergakker]

I would then suggest instead of specifying by visible level you specify criteria and column and build a query using INDIRECT.

Its a little late and I am tired, I could maybe look into this for tomorrow, or you could try yourself.

 

[Tinus Bergakker]

Ok sorry I understand why you want to use SUBTOTAL. You will filter on multiple criteria over levels 1,2,3 say and would like statistics of "Prop" for example.

If this is the case, this is a gonna be a lot of work.

And I would need to spend a few hours on this.

I will help you out though. The formulas for the things you require are:

COUNT = Count of all entries (lets call this n)

AVERAGE = Sum of all entries / n

STDEV = SQRT( (Sum of (entries - AVERAGE) ^ 2 ) / n )

I think you would physically need to work with the formulas to achieve this, I see no easy way out here.

 

[georgepet]

Yes you are right - I will need to insert the ST Dev formula somewhere into the original formulas I have already constructed for Average and Count (posted in my 1st post).

Thank you for your help!

 

[Tinus Bergakker]

Here is how you do it.

Add another column to your data

Assuming your scores are in Column G, add this in column H

SUBTOTAL(102, G2) and copy down

Now use the following

=COUNT(IF(H2:H1000=1,G2:G1000,""))
=AVERAGE(IF(H2:H1000=1,G2:G1000,""))
=STDEV(IF(H2:H1000=1,G2:G1000,""))

and viola it works. Easy formulas too. So now simply modify the above to include if conditions for "Prop", "Hooker" and so forth.

 

[georgepet]

Tinus, great logic. It works - but when I change a filter from Level 1 to Level 2, the 'Average' gives the error #VALUE?

 

[georgepet]

Also, how would I use an IF function to get the answer for Prop or Lock only? Thank you!!

 

[Tinus Bergakker]

Replace the G2:G1000 in the formulas below with this formula

IF(E2:E1000="Prop",G2:G1000,"")

Also I have my final values below the filter and I have been switching between levels, but I cant seem to replicate the #VALUE error.

You have pressed ctrl+shift+enter ? By not pressing this was the only way I could get an error.