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

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

=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

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:

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

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

In , T. Valko
spake thusly:

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

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

Excellent! Thank you very much.

(How does one know when something becomes an array formula, anyway? Conceptually,
I mean. And besides just "if it doesn't work, try Ctrl-Shift-Enter, and if it
now works. well, it was an array formula.) :-)

I think this is the order you want:

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

That is correct.

Oh, a follow-up question is, I was playing with named ranges trying to get a
flexible designation for "lastRow" in these formulas instead of 16 (or the
safe 500 I used), but so far with no luck. Just a minor touch I wouldn't
mind making work, but not important -- what we have so far works well.
I tried (in Names Manager):

colBottom
=OFFSET(MergeSheet!$A$1,lastRow-1,COLUMN()-1)

where "lastRow" is
=COUNTA(MergeSheet!$A:$A)

and then using, in the formula, e.g., SUMPRODUCT(--(B$2:colBottom=B2),...

But no-go.

Regards,
Dallman Ross

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

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

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

Excellent! Thank you very much.

(How does one know when something becomes an array formula, anyway?
Conceptually,
I mean. And besides just "if it doesn't work, try Ctrl-Shift-Enter, and
if it
now works. well, it was an array formula.) :-)

I think this is the order you want:

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

That is correct.

Oh, a follow-up question is, I was playing with named ranges trying to get
a
flexible designation for "lastRow" in these formulas instead of 16 (or the
safe 500 I used), but so far with no luck. Just a minor touch I wouldn't
mind making work, but not important -- what we have so far works well.
I tried (in Names Manager):

colBottom
=OFFSET(MergeSheet!$A$1,lastRow-1,COLUMN()-1)

where "lastRow" is
=COUNTA(MergeSheet!$A:$A)

and then using, in the formula, e.g., SUMPRODUCT(--(B$2:colBottom=B2),...

But no-go.

Regards,
Dallman Ross

Here's one way:

Assuming the data is on Sheet1 in the range B2:Hn and you have a header row
in row 1, B1:H1.

Define the dynamic range as:

Name: rng (or whatever name you want)

Refers to: =OFFSET(Sheet1!$B$2:$H$2,,,COUNTA(Sheet1!$B:$B)-1)

Then the formula becomes (still an array):

=SUMPRODUCT(--(INDEX(rng,,1)=B2),--(INDEX(rng,,3)=D2),--(IF(B2="buy",H2<INDEX(rng,,7),H2INDEX(rng,,7))))+ 1

Whe
INDEX(rng,,1) refers to the 1st column of the named range = column B
INDEX(rng,,3) refers to the 3rd column of the named range = column D
INDEX(rng,,7) refers to the 7th column of the named range = column H

Note: In the formula you see that we are only testing for "buy". I assumed
that there would only be 2 possibilities: Buy or Sell, and nothing else. So
the logic is that if the cell doesn't contain "Buy" then it *must* contain
"Sell".

How to recognize when an array condition exists....that's kind of hard to
explain. I'll say that experience is the best teacher.

See if this helps:

http://cpearson.com/excel/ArrayFormulas.aspx

--
Biff
Microsoft Excel MVP

In , T. Valko
spake thusly:

"Dallman Ross" <dman@localhost. wrote in message
...

Oh, a follow-up question is, I was playing with named ranges
trying to get a flexible designation for "lastRow" in these
formulas instead of 16 (or the safe 500 I used), but so far
with no luck. Just a minor touch I wouldn't mind making work,
but not important -- what we have so far works well. I tried
(in Names Manager):

colBottom
=OFFSET(MergeSheet!$A$1,lastRow-1,COLUMN()-1)

where "lastRow" is
=COUNTA(MergeSheet!$A:$A)

and then using, in the formula, e.g., SUMPRODUCT(--(B$2:colBottom=B2),...

But no-go.

Here's one way:

Assuming the data is on Sheet1 in the range B2:Hn and you have a header row
in row 1, B1:H1.

Define the dynamic range as:

Name: rng (or whatever name you want)

Refers to: =OFFSET(Sheet1!$B$2:$H$2,,,COUNTA(Sheet1!$B:$B)-1)

Then the formula becomes (still an array):

=SUMPRODUCT(--(INDEX(rng,,1)=B2),--(INDEX(rng,,3)=D2),--(IF(B2="buy",H2<INDEX(rng,,7),H2INDEX(rng,,7))))+ 1

Whe
INDEX(rng,,1) refers to the 1st column of the named range = column B
INDEX(rng,,3) refers to the 3rd column of the named range = column D
INDEX(rng,,7) refers to the 7th column of the named range = column H

Hmm, it makes sense, and I tried it, but I can't get it to work. The
logic seems similar to my earlier attempts that also failed despite
my expectation that they would work. I wonder if the problem is that
I need to turn on "Accept labels in formulas" in the program options.
This is just a wild guess, but that option is not currently
checked. I use Excel 2002, for what it's worth.

Anyway, I now put the long "mess" into a "name" and refer to the
name to set the formula in VBA, and that works fine.

Note: In the formula you see that we are only testing for
"buy". I assumed that there would only be 2 possibilities: Buy or
Sell, and nothing else. So the logic is that if the cell doesn't
contain "Buy" then it *must* contain "Sell".

That is correct. I was able to follow your algorithm's logic, as well.
Thank you for checking.

How to recognize when an array condition exists....that's kind of hard to
explain. I'll say that experience is the best teacher.

See if this helps:

http://cpearson.com/excel/ArrayFormulas.aspx

I'll try to read it over in the next number of days. Thanks very much
again!

Dallman

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

"Dallman Ross" <dman@localhost. wrote in message
...

Oh, a follow-up question is, I was playing with named ranges
trying to get a flexible designation for "lastRow" in these
formulas instead of 16 (or the safe 500 I used), but so far
with no luck. Just a minor touch I wouldn't mind making work,
but not important -- what we have so far works well. I tried
(in Names Manager):

colBottom
=OFFSET(MergeSheet!$A$1,lastRow-1,COLUMN()-1)

where "lastRow" is
=COUNTA(MergeSheet!$A:$A)

and then using, in the formula, e.g.,
SUMPRODUCT(--(B$2:colBottom=B2),...

But no-go.

Here's one way:

Assuming the data is on Sheet1 in the range B2:Hn and you have a header
row
in row 1, B1:H1.

Define the dynamic range as:

Name: rng (or whatever name you want)

Refers to: =OFFSET(Sheet1!$B$2:$H$2,,,COUNTA(Sheet1!$B:$B)-1)

Then the formula becomes (still an array):

=SUMPRODUCT(--(INDEX(rng,,1)=B2),--(INDEX(rng,,3)=D2),--(IF(B2="buy",H2<INDEX(rng,,7),H2INDEX(rng,,7))))+ 1

Whe
INDEX(rng,,1) refers to the 1st column of the named range = column B
INDEX(rng,,3) refers to the 3rd column of the named range = column D
INDEX(rng,,7) refers to the 7th column of the named range = column H

Hmm, it makes sense, and I tried it, but I can't get it to work. The
logic seems similar to my earlier attempts that also failed despite
my expectation that they would work. I wonder if the problem is that
I need to turn on "Accept labels in formulas" in the program options.
This is just a wild guess, but that option is not currently
checked. I use Excel 2002, for what it's worth.

Anyway, I now put the long "mess" into a "name" and refer to the
name to set the formula in VBA, and that works fine.

Note: In the formula you see that we are only testing for
"buy". I assumed that there would only be 2 possibilities: Buy or
Sell, and nothing else. So the logic is that if the cell doesn't
contain "Buy" then it *must* contain "Sell".

That is correct. I was able to follow your algorithm's logic, as well.
Thank you for checking.

How to recognize when an array condition exists....that's kind of hard to
explain. I'll say that experience is the best teacher.

See if this helps:

http://cpearson.com/excel/ArrayFormulas.aspx

I'll try to read it over in the next number of days. Thanks very much
again!

Dallman

I wonder if the problem is that I need to turn on "Accept labels in
formulas"

No, that's totally unrelated.

Another way is to create a separate dynamic range for each of the 3 ranges
needed. I thought just using a single dynamic range would be less confusing
(but it does make the formula slightly longer).

--
Biff
Microsoft Excel MVP

In , T. Valko
spake thusly:

"Dallman Ross" <dman@localhost. wrote in message
...

Oh, a follow-up question is, I was playing with named ranges
trying to get a flexible designation for "lastRow" in these
formulas instead of 16 (or the safe 500 I used), but so far
with no luck. Just a minor touch I wouldn't mind making work,

Another way is to create a separate dynamic range for each of
the 3 ranges needed. I thought just using a single dynamic range
would be less confusing (but it does make the formula slightly
longer).

My long VBA project for this use of mine is wrapping up now, after
about 10 days of work and lots of newfound experience at VBA.
I want to thank you and also other people here who responded
in related threads, notably J. Latham, who was also more than
helpful.

I'm sure I'll be tweaking things further in the days, weeks, and
months ahead, but for now, I have a wonderfully table I'll be
using every workday. I may write up parts of the experience
later, and if I do, I'll be sure to post it.

I have a couple of unsolved oddities, and the above is one,
but I'm beginning to suspect a bug or bugs in the program, or
perhaps in an Add-In I have installed. I've now worked around
all these hindrances, but I'll list them for the record. This
is with Excel 2002:

(1) Named ranges that I set with a mixture of relative and absolute
refs go invalid between runs or have the absolute parts shifted as
if they were relative when I try to use the expressions in other
columns. I use the popular Add-In Names Manager, and have the
current version. By way of a workaround, I tried using absolute
refs with INDIRECT and/or OFFSET in these named expressions,
and they fail with an "invalid" error. If I put the identical
expressions in the cells manually, they work as expected.

(2) Some code I enabled that populates columns with conditional-
formatting formulas using relative references for row lines goes
wrong. E.g., I start in Row 2 and have a reference to $E2
and fill that to the bottom row. When the VBA is done, the
table looks wrong; I look in the conditional formats and find
$E3 where I expected $E2. I've double- and triple-checked my
code and my ranges and I don't see any problem. I think there's
a bug somewhere.

(3) The named ranges I tried on my own and the ones you suggested
both would not work. In contrast to the above, when I put these in
the cells manually, they still did work. I now suspect this
is an insidious problem to do with shifting relative references,
analogous to 2. above in another guise.

(4) If I open my workbook and run the macro, it runs fine
and gives the results I want (after I added kludge workarounds
to the above problems, which I'll state below). But if I run the
macro subsequent times, empty, formatted columns (a dozen at a
time or so) are added to the end of the used range. I don't like
them there, because they hinder my navigation, e.g., going to the
last cell. There is no data in the cells. If I close the
sheet and re-open and run the VBA again, they go away. (I
do use a .range.clear function at the top of the macro.)
If I run the VBA multiple times without closing the book,
more and more empty columns are added. I can't figure out what
is causing this, and suspect an Excel bug of some kind. I do
have ADO engines running, etc., and have read that this can
interact with Excel in odd ways, for example.

To solve these problem by kludge method, here's what I did:

(1) I used my mixed-ref named expression in one column, writing
the results to the column successfully and using an "invisible"
font; then, in the other columns in which I want to operate on
the same calculated data, I simply reference the first column's
relative rows instead of using the named expressions again
where there the relative references would otherwise shift.

(2) In the conditional formulas, I was able to apply OFFSET()
instead of relative row refs and make it work as desired.

(3) Shifting the whole long array formula to a named expression
did work, though applying the named ranges within that did not.
Since the formula is out of my way as a named expression and
I can reference it that way, I'm satisfied for now. Again,
I suspect oddly shifting relative refs as the reason for my
trouble with the named ranges.

(3a) Could it be that I've exceeded a length limit on named
expressions, and am causing my problems that way by, I might
speculate, destroying pointer references in the compiler, etc.?
I will have to look more at this idea tomorrow.

(4) My solution to the unwanted columns is this bit of code:
(Have others found a similar problem sometimes? I tried Google ...)

' ouch! This is a kludge fix for a dumb bug
Dim theirRow, theirCol As Long

myRow = lastRow(DestSh) ' sh.Cells.Find(What:="*"...
myCol = lastCol(DestSh) ' sh.Cells.Find(What:="*"...

theirRow = .Cells.SpecialCells(xlLastCell).Row
theirCol = .Cells.SpecialCells(xlLastCell).Column

Debug.Print myRow, myCol, theirRow, theirCol

If theirCol myCol Then _
.Range(Cells(1, myCol + 1), _
Cells(theirRow, theirCol)).Delete

--
Regards,
Dallman Ross

In , Dallman Ross <dman@localhost.
spake thusly:

My long VBA project for this use of mine is wrapping up now,
after about 10 days of work and lots of newfound experience
at VBA. I want to thank you and also other people here who
responded in related threads, notably J. Latham, who was also
more than helpful.

I'm sure I'll be tweaking things further in the days, weeks,
and months ahead, but for now, I have a wonderfully table I'll
be using every workday. I may write up parts of the experience
later, and if I do, I'll be sure to post it.

For starters, here's an abbreviated view of the sheet. I changed
some data, e.g., order numbers, to protect the innocent. :-)
(Only showing a sample of the data here.)

http://www.heliotropos.com/xl/tmp/OpenOrders.jpg

--
dman