How to determine following function in Excel?

I would like to set following conditions in Excel, but find it difficult on
coding.
Could anyone give me any suggestion please?

Under A Colume, there are a list of numbers
Profit
100
101
-50
-60
50
-50
-20
-10
80

Under Colume B, I would like to determine the maximum consecutive loss from
Colume A, in this case, it should return -50-60 = -110

Does anyone have any suggestion?
Thank you in advance
Eric

Hi Eric:

In B2 enter =A1+A2
and copy B2 down thru B9. You should see:

100
101 201
-50 51
-60 -110
50 -10
-50 0
-20 -70
-10 -30
80 70


In an un-used cell enter =MIN(B2:B9)
--
Gary's Student


"Eric" wrote:

> I would like to set following conditions in Excel, but find it difficult on
> coding.
> Could anyone give me any suggestion please?
>
> Under A Colume, there are a list of numbers
> Profit
> 100
> 101
> -50
> -60
> 50
> -50
> -20
> -10
> 80
>
> Under Colume B, I would like to determine the maximum consecutive loss from
> Colume A, in this case, it should return -50-60 = -110
>
> Does anyone have any suggestion?
> Thank you in advance
> Eric

Try =MIN(A1:A8+A2:A9) which must be entered as an array formula using
SHIFT+CTRL+ENTER rather than just ENTER.

The formula will appear like this in Formula Bar: {=MIN(A1:A8+A2:A9)}
Of course, you can change ranges to fit you need: A1:A99+A2:A100
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Eric" <Eric@discussions.microsoft.com> wrote in message
news:37C2FA6F-CDB4-439B-B3E2-9F44A166F099@microsoft.com...
>I would like to set following conditions in Excel, but find it difficult on
> coding.
> Could anyone give me any suggestion please?
>
> Under A Colume, there are a list of numbers
> Profit
> 100
> 101
> -50
> -60
> 50
> -50
> -20
> -10
> 80
>
> Under Colume B, I would like to determine the maximum consecutive loss
> from
> Colume A, in this case, it should return -50-60 = -110
>
> Does anyone have any suggestion?
> Thank you in advance
> Eric

"Eric" wrote:
> Under A Colume, there are a list of numbers
> Profit
> 100
> 101
> -50
> -60
> 50
> -50
> -20
> -10
> 80
> Under Colume B, I would like to determine the maximum
> consecutive loss from Colume A, in this case, it should
> return -50-60 = -110

Clarification needed ....

For the 3 rows starting in the 4th row from the end, do
you want -50-20-10 = -80? And you would want that
to be the "maximum consecutive loss" if -60 were -20
in the 4th row (so that -50-20 = -70, not -110), right?

In other words, you are not just interested in pairwise
"consecutive" losses, but consecutive loss sequences of
any length. Is that right?

Thank everyone for suggestion
Yes, I am interested on the consecutive loss sequences of any length, and
return the highest loss for the result.
Thank you
Eric

"joeu2004@hotmail.com" wrote:

> "Eric" wrote:
> > Under A Colume, there are a list of numbers
> > Profit
> > 100
> > 101
> > -50
> > -60
> > 50
> > -50
> > -20
> > -10
> > 80
> > Under Colume B, I would like to determine the maximum
> > consecutive loss from Colume A, in this case, it should
> > return -50-60 = -110
>
> Clarification needed ....
>
> For the 3 rows starting in the 4th row from the end, do
> you want -50-20-10 = -80? And you would want that
> to be the "maximum consecutive loss" if -60 were -20
> in the 4th row (so that -50-20 = -70, not -110), right?
>
> In other words, you are not just interested in pairwise
> "consecutive" losses, but consecutive loss sequences of
> any length. Is that right?

in B2
=IF(ROW()=2,IF(SIGN(A1)<>SIGN(A2),A2,A1+A2),IF(SIGN(A1)<>SIGN(A2),A2,A2+B1))

Then select b2 and drag down the column

in C1
=min(B:B)

--
Regards,
Tom Ogilvy


"Eric" <Eric@discussions.microsoft.com> wrote in message
news:36628763-840D-45DF-9FC0-892AFCACA137@microsoft.com...
> Thank everyone for suggestion
> Yes, I am interested on the consecutive loss sequences of any length, and
> return the highest loss for the result.
> Thank you
> Eric
>
> "joeu2004@hotmail.com" wrote:
>
> > "Eric" wrote:
> > > Under A Colume, there are a list of numbers
> > > Profit
> > > 100
> > > 101
> > > -50
> > > -60
> > > 50
> > > -50
> > > -20
> > > -10
> > > 80
> > > Under Colume B, I would like to determine the maximum
> > > consecutive loss from Colume A, in this case, it should
> > > return -50-60 = -110
> >
> > Clarification needed ....
> >
> > For the 3 rows starting in the 4th row from the end, do
> > you want -50-20-10 = -80? And you would want that
> > to be the "maximum consecutive loss" if -60 were -20
> > in the 4th row (so that -50-20 = -70, not -110), right?
> >
> > In other words, you are not just interested in pairwise
> > "consecutive" losses, but consecutive loss sequences of
> > any length. Is that right?