How to determine following function in Excel?

E

Eric

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
 
G

Gary''s Student

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)
 
B

Bernard Liengme

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
 
J

joeu2004

Eric said:
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
Click to expand...

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?
 
E

Eric

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
 
T

Tom Ogilvy

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)
 
Top