"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-39B9F9.17161419102005@msnews.microsoft.com...
> Asumptions:
>
> B2:B100 contains the 'Category'
>
> G2:G100 contains the 'Calculation'
>
> Column G is ranked from highest to lowest, based on category
>
> Formula:
>
> H2, copied down:
>
> =SUMPRODUCT(--($B$2:$B$100=B2),--(G2<$G$2:$G$100))+1
>
> Hope this helps!
>


thanks, but does that only work if Col G is ranked highest to lowest? I
don't want to have to do any sorting if possible, just all automatic


lets say


Ortiz, Red Sox, blah, blah, blah, result of blah = 7 (I want rank: 2)
A-Rod, Yankees, blah, blah, blah, result of blah = 5 (I want rank: 2)
Manny, Red Sox, blah, blah, blah, result of blah = 8 (I want rank: 1)
Varitek, Red Sox, blah, blah, blah, result of blah = 3 (I want rank: 3)
Sheffield, Yankees, blah, blah, blah, result of blah = 10 (I want rank: 1)


The data is coming straight from the web, there is no guarantee (in fact it
won't be) that the list will be sorted by category, nor will the order be
sorted, eg, Mannys "result" might be higher than Ortiz in the example above

will your formula work, given the example above? (actually my data is NFL
stats but the same principle applies, the stats are imported, I have a
column to work out a composite value, I want a rankif type column that
automatically says what rank each player is on their team, regardless of the
order they're listed, or mixed up)