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

try (asumes data starts in row2):

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

"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

First thing I would do is get rid of all the "at" signs!

Buy 100 CACH @14.00 2 of 3

How do you get a rank of 2 for that? Seems to me it should be either 1 or 3
depending on what rank order you want.

It looks like the price is already sorted descending and there are no
duplicates for any symbol. How do you want to rank any duplicates?

Buy 100 CACH @14.00
Buy 100 CACH @13.66
Buy 330 CACH @13.66

What ranks would those 3 get?

--
Biff
Microsoft Excel MVP


"Dallman Ross" <dman@localhost. wrote in message
...
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

In , T. Valko
spake thusly:

First thing I would do is get rid of all the "at" signs!

The "@" signs are not in the numbers, merely in the formatting.
That is, those are plain numbers (prices); but I formatted them
custom as follows so I would see and "@" sign in front and know
at a glance which column I'm looking at -- the column representing
my limit order's price. FYI, formatting is done this way:

_("@"* #,##0.00_);_("@"* (#,##0.00);_($* "-"??_);_(@_)

But even if the "@" signs were really there in the data and not
just a format representation, should it matter to a
SUMPRODUCT expression that can match on strings? My thought
is that it wouldn't, but I haven't tested that.

Buy 100 CACH @14.00 2 of 3

How do you get a rank of 2 for that? Seems to me it should be
either 1 or 3 depending on what rank order you want.

Yes, it was just an example but was not correct given the
data I showed. Sorry about the confusion. As for which direction,
while of course it matters, it seemed to me that proposed
solutions would be easily customizable either way. In fact,
the buy orders and the sell orders have the ranking switched!

I was able to get your suggested formula from the other reply
article to work, and it's great. (I'll post separately about that.
Thanks, Biff!) What I've done for now is switched your "<" to a
"" for the sell orders, and that switches the rank as I'd hoped.
I'll try later to figure out a way to have it be self-adjusting so
I don't have to enter different formulas for different types of
orders. I suppose I'd use an IF statement; not sure yet of the
best approach there.

The reason the ranking is inverted for buys and sells is, these are
open -- usually GTC ("good-till-cancel") -- limit orders to buy or
sell. If it's an unexecuted buy order, it stands to reason that
the current price of the stock is somewhere above that price now.
(Otherwise, the order would have executed, and it would thus no
longer be open.) So the highest price in the list is the first one
that will execute, provided the stock's price ever falls far enough
for that buy to happen.

Conversely, the first open sell order to execute from a price-stacked
set will be the one at the lowest offering price.

I didn't want to get so complicated as to state all this in
the post where I asked how to do it, because it would just
add levels of confusion, I thought. But since you asked . . . :-)

It looks like the price is already sorted descending and there
are no duplicates for any symbol. How do you want to rank any
duplicates?

They're sorted as I showed them in the sample, true, but there
is no guarantee that they'll stay that way. I sort the table
in different ways to view different studies of the data.

Buy 100 CACH @14.00
Buy 100 CACH @13.66
Buy 330 CACH @13.66

What ranks would those 3 get?

If the price is the same, the rank doesn't really matter to
me, as both will likely get executed when we reach that price
(provided there is enough action at the price to fill both
orders; sometimes there is, indeed, partial or non-execution).
However, it's very rare for me to have two orders priced identically.
Very occasionally it happens, for odd reasons. But the rank
is irrelevant then, because both have an equal chance of triggering
first. To answer you completely, though, the $14.00 one should
be first, as when the price falls from where it presently sits
somewhere "north" of there, that one will fill first. Just
to give more insight than you probably wanted, here is the
data from Friday's close in that stock:

1:39am [~] 585[0] quote usa cach

Price Last High Low Net % Volume
----- ------ ----- ----- ----- ----- ------
CACH: 14.90 4:00pm 15.02 14.68 -0.10 -0.67 182325

Another ("too-much-information," but interesting, at least
to me) point is, there can be situations when all would execute
at once or in no particular order. Suppose there is bad
news or a general market spike down. The stock could open
what we call "gap-down" the next trading day. Suppose it
opens Monday with an asking price of $10? Well, all three
of my limit orders would execute (in unpredictable order within
microseconds of each other), assuming, again, there
is enough price action to fill all orders.

The information I've provided is merely for conversation, and
not explicitly needed to solve my problem (which you seem to
have solved, meanwhile, in any event). Thanks!

Dallman

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

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

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

In , T. Valko
spake thusly:

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


That's perfect. Just what the doctor ordered. Works ideally!
Thank you very much indeed. The ties are fine with me, as well.

I misstated the column letters: I wrote B, D, and H, but
they are actually C, E, and H. But it didn't matter, since
you didn't pay exact attention to that part of my post anyway
and used A, C, and D. :-) :-) No problem, it was easy enough
for me to figure out how to alter that as needed. Your
solution is elegant and just what I was looking for! Thank
you again.

As for the "Toppers" fellow you were addressing with your first line
from up above, oddly, I don't see his post in this newsgroup.
Maybe he canceled his article, or maybe some problem caused
it not to appear on my news server.

I see the part of it you quoted, however. Interesting approach
he took. Looks, from your quoted text, as if there is at least
one other reply I didn't see as well. Hmm -- that's odd, and
I didn't know my server was missing articles from this group.

Regards,
Dallman

======================================
[Quoting the lower part of your article for context]

"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:

[snip]

In , T. Valko
spake thusly:

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

In my follow-up just now, I forgot to discuss that I
switched the "<" to "" for sell orders to flip the ranking
on its head for them. I explained my reasoning in a different
post in this thread. I'm now trying to decide on an efficient
way to automate that. A long IF statement seems ugly but
probably will be the easiest approach.

Regards,
Dallman Ross

"Dallman Ross" <dman@localhost. wrote in message
...
In , T. Valko
spake thusly:

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

In my follow-up just now, I forgot to discuss that I
switched the "<" to "" for sell orders to flip the ranking
on its head for them. I explained my reasoning in a different
post in this thread. I'm now trying to decide on an efficient
way to automate that. A long IF statement seems ugly but
probably will be the easiest approach.

Regards,
Dallman Ross

Not much longer but it now becomes an array formula**:

=SUMPRODUCT(--(B$2:B$16=B2),--(D$2:D$16=D2),--(IF(B2="buy",H2<H$2:H$16,H2H$2:H$16)))+1

I think this is the order you want:

Buy = highest price ranks 1
Sell = lowest price ranks 1

If I have it backwards just "flip" the < and .

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

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

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

"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