contiguous cells

[sbdivemaster]

Does anyone know if there is a way to have a formula only d
calculations on contiguous cells?

I'm using Excel 2000, and I will give you an example:

Here is a column of numbers representing gains and losses:

25
-16
-24
18
16
24
- 8
34
15
9
12
- 4
-13
-14
2
15
- 4


Basically, I want to create a formula that computes the longest strin
of consecutive gains (or losses) in this column. In this example, th
result for gains would be "4" (the most consecutive gains was 4), an
losses would be "3" the most consecutive losses was 3). Is there a wa
to indicate that I only want to include contiguous cells in
calculation

 

[Myrna Larson]

Assuming your data starts at A1, in B1 put this formula:

=SIGN(A1)

In B2:

=IF(SIGN(A2)=SIGN(A1),B1+SIGN(A2),SIGN(A2))

and copy the formula down. With your sample data, it went down to B17.

Then =-MIN(B1:B17) gives you the longest string of losses

and =MAX(B1:B17) gives you the longest string of gains

Modify those formulas to point to the entire list in column B. This won't work
to give the longest string of "no gain/loss", i.e. 0 entries.

 

[hgrove]

sbdivemaster wrote...
...

Basically, I want to create a formula that computes the longest
string of consecutive gains (or losses) in this column. In this
example, the result for gains would be "4" (the most
consecutive gains was 4), and losses would be "3" the most
consecutive losses was 3). Is there a way to indicate that I only
want to include contiguous cells in a calculation?

See

http://www.google.com/groups?selm=3Kdl7.11511$KV3.936701@bgtnsc04-news.ops.worldnet.att.net

and

http://www.google.com/[email protected]

 

[dave]

one way is...

I put your list in a2:a18. Put this formula in cell b2
and copy down(make sure b1 is blank):

=IF(A2<0,IF(D5<0,D5-1,-1),IF(D5>0,D5+1,1))

then just summarize as follows:

most consecutive
gains 4[=MAX($B$2:$B$18)]
losses -3[=MIN($B$2:$B$18)]


hth,
dave