Calculate Stanine Values

[Saxman]

Is there a function to calculate stanine values in Excel?

If so, could this be done en-bloc? By this I mean, calculate all
columns simultaneously, rather than individually.

See below. I require the stanine value for each horse in each column.


Horse OR Or1 Or2 Or3 Or4 Or5 Or6
KIMBALI 73 84 83 80 78 75 75
LLEWELLYN 74 70 71 71 75 74 74
PARAMOUR 70 74 74 73 72 71 70
SILVERWARE 69 78 77 77 77 75 72
CHISWICK BEY 72 81 80 77 74 74 74
PARTNER 68 81 79 76 73 70 70
COTTAM STELLA 45 60 49 47 46 51 51
BABY JUDGE 42 47 45 43 49 49 -
ROLEN SLY 40 - - 45 45 45 46
VOGARTH 36 39 39 46 46 46 36

 

[joeu2004]

Is there a function to calculate stanine values in Excel?
If so, could this be done en-bloc? By this I mean, calculate
all columns simultaneously, rather than individually. [....]
I require the stanine value for each horse in each column. Horse
OR Or1 Or2 Or3 Or4 Or5 Or6
KIMBALI 73 84 83 80 78 75 75
LLEWELLYN 74 70 71 71 75 74 74
PARAMOUR 70 74 74 73 72 71 70
SILVERWARE 69 78 77 77 77 75 72
CHISWICK BEY 72 81 80 77 74 74 74
PARTNER 68 81 79 76 73 70 70
COTTAM STELLA 45 60 49 47 46 51 51
BABY JUDGE 42 47 45 43 49 49 -
ROLEN SLY 40 - - 45 45 45 46
VOGARTH 36 39 39 46 46 46 36

If your data are in A1:H11 (A1=Horse; H11=36), I think the following does
what you want.

Enter the following formula into B13, then copy and paste into B13:H22.

=IF(B2="-","-",LOOKUP(PERCENTRANK($B$2:$H$11,B2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))

Note: Alternatively to copy-and-pasting the formula, select B13:H22, type
the formula in the Formula Bar, then press ctrl+Enter instead of just Enter
(and not ctrl+shift+Enter). Ctrl+Enter copies the normal (non-array)
formula into all selected cells.

 

[Saxman]

Is there a function to calculate stanine values in Excel?
If so, could this be done en-bloc? By this I mean, calculate
all columns simultaneously, rather than individually. [....]
I require the stanine value for each horse in each column. Horse OR
Or1 Or2 Or3 Or4 Or5 Or6
KIMBALI 73 84 83 80 78 75 75
LLEWELLYN 74 70 71 71 75 74 74
PARAMOUR 70 74 74 73 72 71 70
SILVERWARE 69 78 77 77 77 75 72
CHISWICK BEY 72 81 80 77 74 74 74
PARTNER 68 81 79 76 73 70 70
COTTAM STELLA 45 60 49 47 46 51 51
BABY JUDGE 42 47 45 43 49 49 -
ROLEN SLY 40 - - 45 45 45 46
VOGARTH 36 39 39 46 46 46 36

If your data are in A1:H11 (A1=Horse; H11=36), I think the following
does what you want.

Enter the following formula into B13, then copy and paste into B13:H22.

=IF(B2="-","-",LOOKUP(PERCENTRANK($B$2:$H$11,B2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))

Note: Alternatively to copy-and-pasting the formula, select B13:H22,
type the formula in the Formula Bar, then press ctrl+Enter instead of
just Enter (and not ctrl+shift+Enter). Ctrl+Enter copies the normal
(non-array) formula into all selected cells.

I get a zero result for all of these using the second method. The first
method gives me a result, but only in cell B13.

I made an error myself. The first column (OR) is the Official Rating.
That needs to be calculated top to bottom to find its worth against the
other horses.

Or1, Or2, Or3, Or4, Or5, Or6 need to be calculated left to right, as
that is the OR value for each horse over the last six runs.

That would require two calculations. One for each horse over today's
race and one for each horse's last six runs.

 

[joeu2004]

If your data are in A1:H11 (A1=Horse; H11=36), I think the following
does what you want.
Enter the following formula into B13, then copy and paste into B13:H22.
=IF(B2="-","-",LOOKUP(PERCENTRANK($B$2:$H$11,B2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))
Note: Alternatively to copy-and-pasting the formula, select B13:H22,
type the formula in the Formula Bar, then press ctrl+Enter instead of
just Enter (and not ctrl+shift+Enter).

[....]
I get a zero result for all of these using the second method.
The first method gives me a result, but only in cell B13.

Sounds like a mistake in following directions. The ctrl+Enter shortcut
(second method) is tricky. I don't recommend it for you. With the first
method, I suspect you made a mistake with the type of references; for
example, perhaps you typed B2:H11 instead of $B$2:$H$11.

I made an error myself. The first column (OR) is the Official Rating.
That needs to be calculated top to bottom to find its worth against the
other horses.

Or1, Or2, Or3, Or4, Or5, Or6 need to be calculated left to right, as that
is the OR value for each horse over the last six runs.

Download the file stanine.xls from
https://app.box.com/s/go9fe4xvvart2l3xgbx2.

The "overall" worksheet has the original formula based on your original
requirements. You might use that to see your original mistake.

The "revised" worksheet has new formulas for the revised requirements, as I
understand them.

The stanines for this example are of dubious value. In the OR column, you
are distributing 10 values across 9 categories. In the OrX columns, you are
distributing just 6 values(!). The stanine exhibit significant quantization
"error". We cannot expect a normal distribution of the stanines.

To help understand this, I include a table of the PercentRank values.

But perhaps your actual data is much more numerous.

For other readers, the data and formulas are described succinctly below.

The data are in B2:H11.

The formulas in B13:B22 are (B13 for example):
=IF(B2="-","-",LOOKUP(PERCENTRANK($B$2:$B$11,B2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))

The formulas in B14:H22 are (B14 for example):
=IF(C2="-","-",LOOKUP(PERCENTRANK($B2:$H2,C2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))

Similar formulas are in B25:H34, using only PERCENTRANK, not
LOOKUP(PERCENTRANK(...),...).

 

[joeu2004]

Download the file stanine.xls from
https://app.box.com/s/go9fe4xvvart2l3xgbx2. [....]
The formulas in B14:H22 are (B14 for example):
=IF(C2="-","-",LOOKUP(PERCENTRANK($B2:$H2,C2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))

Those formulas are in C13:H22 (C13 for example).

 

[Saxman]

Download the file stanine.xls from
https://app.box.com/s/go9fe4xvvart2l3xgbx2. [....]
The formulas in B14:H22 are (B14 for example):
=IF(C2="-","-",LOOKUP(PERCENTRANK($B2:$H2,C2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))

Those formulas are in C13:H22 (C13 for example).

Thank you very much. I've been looking around an old watermill this
evening. I'll will look at this in the morning and report back.

 

[Saxman]

Download the file stanine.xls from
https://app.box.com/s/go9fe4xvvart2l3xgbx2. [....]
The formulas in B14:H22 are (B14 for example):
=IF(C2="-","-",LOOKUP(PERCENTRANK($B2:$H2,C2),
{0,0.04,0.11,0.23,0.4,0.6,0.77,0.89,0.96},{1,2,3,4,5,6,7,8,9}))

Those formulas are in C13:H22 (C13 for example).

That works a treat. Thank you very much. I just need to monitor things
for awhile.

SKYTRAIN won this today. It had 2 good recent values. See below.


AMADEUS WOLFE 9 9 7 6 5 1 1
FARLOW 7 5 5 9 5 4 3
PIDDIE'S POWER 6 3 1 5 5 5 5
AZRAEL 5 3 3 1 5 5 7
POISSON D'OR 5 9 4 1 1 6 6
CONSIGN 7 - 5 1 1 6 7
BALTI'S SISTER 3 1 3 3 5 9 6
SKYTRAIN 4 3 1 4 4 9 7
BENONI 1 5 5 3 1 9 5
FRONT PAGE NEWS 1 9 5 4 1 1 6