Rank with an array formula
 

Hello,

I would like to find the rank of database entries based on profits, but
by industry as well. Example:

Industry: Profits: Rank:
Software 5 1
Commercial Banking 8 1
Energy 10 1
Energy 8 2
Software 4 2
Energy 5 3
Consumer Electronics 2 1

The "Rank" column is the one I am trying to create. I attempted to do
this by using the array formula "=rank([profits],if([industry
array]=[industry],[profits array]),0)", but got a #VALUE! error for
some reason. Similar formulas work using min, max, etc., so I am
confused as to why this is returning an error. I know that this task
could be done manually by sorting, but I would like to avoid that
method if possible. Any ideas of ways to work around this error, or
accomplish my goal using different formulas?

Rank with an array formula
 

=MATCH(B2,LARGE(IF($A$2:$A$20=A2,$B$2:$B$20),ROW(I NDIRECT("1:"&COUNTIF($A$2:
$A$20,A2)))),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tim" wrote in message
oups.com...
Hello,

I would like to find the rank of database entries based on profits, but
by industry as well. Example:

Industry: Profits: Rank:
Software 5 1
Commercial Banking 8 1
Energy 10 1
Energy 8 2
Software 4 2
Energy 5 3
Consumer Electronics 2 1

The "Rank" column is the one I am trying to create. I attempted to do
this by using the array formula "=rank([profits],if([industry
array]=[industry],[profits array]),0)", but got a #VALUE! error for
some reason. Similar formulas work using min, max, etc., so I am
confused as to why this is returning an error. I know that this task
could be done manually by sorting, but I would like to avoid that
method if possible. Any ideas of ways to work around this error, or
accomplish my goal using different formulas?

Rank with an array formula
 

Thanks Bob!

Bob Phillips wrote:
=MATCH(B2,LARGE(IF($A$2:$A$20=A2,$B$2:$B$20),ROW(I NDIRECT("1:"&COUNTIF($A$2:
$A$20,A2)))),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tim" wrote in message
oups.com...
Hello,

I would like to find the rank of database entries based on profits, but
by industry as well. Example:

Industry: Profits: Rank:
Software 5 1
Commercial Banking 8 1
Energy 10 1
Energy 8 2
Software 4 2
Energy 5 3
Consumer Electronics 2 1

The "Rank" column is the one I am trying to create. I attempted to do
this by using the array formula "=rank([profits],if([industry
array]=[industry],[profits array]),0)", but got a #VALUE! error for
some reason. Similar formulas work using min, max, etc., so I am
confused as to why this is returning an error. I know that this task
could be done manually by sorting, but I would like to avoid that
method if possible. Any ideas of ways to work around this error, or
accomplish my goal using different formulas?