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?
|