Rankif?
For a unique ranking, try...
H2, copied down:
=(SUMPRODUCT(--($B$2:$B$100=B2),--(G2<$G$2:$G$100))+1)+(SUMPRODUCT(--($B$
2:B2=B2),--($G$2:G2=G2))-1)
To rank based on a second column of calculations, let's say Column F,
try...
H2, copied down:
=(SUM(($B$2:$B$100=B2)*(G2<$G$2:$G$100))+1)+IF(SUM (($B$2:$B$100=B2)*($G$2
:$G$100=G2))1,MATCH(F2,LARGE(IF(($B$2:$B$100=B2)* ($G$2:$G$100=G2),$F$2:$
F$100),ROW(INDIRECT("1:"&SUM(($B$2:$B$100=B2)*($G$ 2:$G$100=G2))))),0)-1)
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that
'Column F' is also ranked from highest to lowest.
Hope this helps!
In article ,
"Lee Harris" wrote:
well, that DID seem to work for my data - THANK YOU
the only problem now is how to resolve ties, but have no idea how to do that
even if I had a second column of calculations on which to base the
tiebreaker. any ideas?
|