ranking in a dynamic range help needed??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

barkiny - 15 May 2006 13:07 GMT

i have in column A there are companies
in column B there are values
in column C there are periods

i want to find the relative position of each company (RANK) referred to
each period

the problem is i dont know the range of the periods

can you add a dynamic range formula into ranking formula

thanks in advance

+-------------------------------------------------------------------+

|Filename: sheet2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4761 |

+-------------------------------------------------------------------+

Signature

barkiny

Reply to this Message

barkiny - 15 May 2006 15:36 GMT

i used

CellD1=

=SUMPRODUCT((B$2:B$10=B2)*(C$2:C$10>C2))+1

but it returns the same rank for same values

do you know how to assign different rank for Sumproduct formula

thanks in advance

Signature

barkiny

Reply to this Message

barkiny - 15 May 2006 17:21 GMT

I found the answer of my question

you can paste
=SUMPRODUCT((B$2:B$10=B2)*(C$2:C$10>C2))+SUMPRODUCT((B$2:$B2=B2)*(C$2:$C2=C2))

it is an array formula
control+shift+enter

Signature

barkiny

Reply to this Message