Is there a way I can do effectively a "rankif" formula via some array
trickery?

I've set up Excel to import web data into a table,

let's say COl A is the names, Col B is some kind of category, Cols C-F are
data, and in Col G I do some calculation based on cols C-F.

What I want to do is be able to automatically have a ranking but within a
category, and have that in Col H, so the ranks 1,2,3,4 etc appear for each
individual category, there will be different items in each category and the
list might even be mixed up, ie not in category order

does that make sense?

thx in advance

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!

In article ,
"Lee Harris" wrote:

Is there a way I can do effectively a "rankif" formula via some array
trickery?

I've set up Excel to import web data into a table,

let's say COl A is the names, Col B is some kind of category, Cols C-F are
data, and in Col G I do some calculation based on cols C-F.

What I want to do is be able to automatically have a ranking but within a
category, and have that in Col H, so the ranks 1,2,3,4 etc appear for each
individual category, there will be different items in each category and the
list might even be mixed up, ie not in category order

does that make sense?

thx in advance

"Domenic" wrote in message
...
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)

"Lee Harris" wrote in message
...

"Domenic" wrote in message
...
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)




well, that DID seem to work for my data - THANK YOU
the only problem now is how to resolve ties, but have no idea how to do that
even if I had a second column of calculations on which to base the
tiebreaker. any ideas?

For a unique ranking, try...

H2, copied down:

=(SUMPRODUCT(--($B$2:$B$100=B2),--(G2<$G$2:$G$100))+1)+(SUMPRODUCT(--($B$
2:B2=B2),--($G$2:G2=G2))-1)

To rank based on a second column of calculations, let's say Column F,
try...

H2, copied down:

=(SUM(($B$2:$B$100=B2)*(G2<$G$2:$G$100))+1)+IF(SUM (($B$2:$B$100=B2)*($G$2
:$G$100=G2))1,MATCH(F2,LARGE(IF(($B$2:$B$100=B2)* ($G$2:$G$100=G2),$F$2:$
F$100),ROW(INDIRECT("1:"&SUM(($B$2:$B$100=B2)*($G$ 2:$G$100=G2))))),0)-1)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that
'Column F' is also ranked from highest to lowest.

Hope this helps!

In article ,
"Lee Harris" wrote:

well, that DID seem to work for my data - THANK YOU
the only problem now is how to resolve ties, but have no idea how to do that
even if I had a second column of calculations on which to base the
tiebreaker. any ideas?