Rankif?

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 <[email protected]>,
"Lee Harris" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
>> 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 <[email protected]>,
"Lee Harris" <[email protected]> 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?

The previous formula works fine when you want to rank from maximum value (= ranking no. 1) to minimum value (ranking 'n')

What I want to do is to rank the results in a time trial, where the winner is the person with the smallest time

I have no idea how the formula below works (but I can see that it does work on sample data that I am using for testing); can anyone help so that the ranking works in reverse to the formula below?

Many thanks

Piers

Sorry - I forgot to say that I am using Excel 2007 (if that makes any difference)

Piers

I have sorted this through a bit of trial and error

By switching the '>' to '<' in the second set of brackets, this reverses the sortation order from smallest-to-largest

Not sure how, but it does it!