Rank with an array formula

[Tim]

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?

 

[Bob Phillips]

=MATCH(B2,LARGE(IF($A$2:$A$20=A2,$B$2:$B$20),ROW(INDIRECT("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]

Thanks Bob!

=MATCH(B2,LARGE(IF($A$2:$A$20=A2,$B$2:$B$20),ROW(INDIRECT("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)

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?