ranking in a dynamic range help needed??

  • Thread starter Thread starter barkiny
  • Start date Start date
B

barkiny

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 |
+-------------------------------------------------------------------+
 
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
 
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
 
Back
Top