Biff,
That's why you are an MVP and I'm not (nor could be)!

I would never have thought of your approach using the last comparison
(D2<D$2:D$16) but I'll try and remember for the future! You learn something
new here everyday thanks to people like yourself.


"T. Valko" wrote:

Think you're making it more complicated than need be.

=SUMPRODUCT(--(A$2:A$16=A2),--(C$2:C$16=C2),--(D2<D$2:D$16))+1

And, if they really do want the result to be like this: 1 of 3

=SUMPRODUCT(--(A$2:A$16=A2),--(C$2:C$16=C2),--(D2<D$2:D$16))+1
&" of "&SUMPRODUCT(--(A$2:A$16=A2),--(C$2:C$16=C2))

These will emulate the RANK function as far as ties:

14 = 1.....1 of 3
13 = 2.....2 of 3
13 = 2.....2 of 3

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
See Biff's reply re duplicates : I'll leave him to provide an answer ..
he's
much smarter than me!

"Toppers" wrote:



Wrong ....!!!!

=RANK(D2,OFFSET($H$2,MATCH(1,(A2=$A$2:$A$16)*(C2=$ C$2:$C$16),0)-1,0,SUMPRODUCT(--($A$2:$A$16=A2),--($C$2:$C$16=C2)),1),0)

Enter with Ctrl+Shift+Enter

"Dallman Ross" wrote:

Given a workseeht table sort of like this:

B C D ... H
--- --- ---- ------
Buy 100 CACH @14.00
Buy 100 CACH @13.75
Buy 330 CACH @13.66
Buy 210 CAVM @23.66
Buy 220 CAVM @22.86
Buy 230 CHKP @22.03
Buy 270 CMVT @18.56
Buy 180 CREE @27.56
Buy 190 CREE @26.96
Buy 190 CREE @26.26
Buy 200 CSCO @25.46
Sell 30 CACH @22.53
Sell 220 CACH @21.66
Sell 230 CACH @20.80
Sell 240 CACH @19.94
...


I want to have a Column Q, such that each stock's Buy
or Sell orders are ranked by price. E.g.,

B C D ... H ... Q
--- --- ---- ------ ------
Buy 100 CACH @14.00 2 of 3
...
Sell 220 CACH @21.66 3 of 4

I imagine solutions might include SUMPRODUCT among Columns
B, D, and H, but I can't quite figure out how to do it.
Help would be most appreciated!

Dallman