Saxman said:

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.

Saxman said:

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(...),...).