Formula to stop adding at a positive number
 

I need help with the formula to stop adding when the cell number becomes a
positive number. I am looking at different senarios for a breakeven point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change another
calculation.

Make Sense?

Formula to stop adding at a positive number
 

Try this:

=SUMIF(B1:G1,"<0")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"nkhelp" wrote in message
...
I need help with the formula to stop adding when the cell number becomes a
positive number. I am looking at different senarios for a breakeven point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change another
calculation.

Make Sense?

Formula to stop adding at a positive number
 

Use =SUMIF(B1:G1,"<0")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"nkhelp" wrote in message
...
I need help with the formula to stop adding when the cell number becomes a
positive number. I am looking at different senarios for a breakeven
point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change
another
calculation.

Make Sense?

Formula to stop adding at a positive number
 

=SUM(B2:OFFSET(B2,0,MATCH(0,B2:G2)-1))
or to get a positive number
=-SUM(B2:OFFSET(B2,0,MATCH(0,B2:G2)-1))
--
Don Guillett
SalesAid Software

"nkhelp" wrote in message
...
I need help with the formula to stop adding when the cell number becomes a
positive number. I am looking at different senarios for a breakeven
point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change
another
calculation.

Make Sense?

Formula to stop adding at a positive number
 

What about when/if the cash flow becomes 0? Is that considered positive?

-10 -5 -4 0 2 4

Try this. Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=IF(B10,0,SUM(B1:INDEX(B1:G1,MATCH(TRUE,B1:G1=0, 0)-1)))

Biff

"nkhelp" wrote in message
...
I need help with the formula to stop adding when the cell number becomes a
positive number. I am looking at different senarios for a breakeven
point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change
another
calculation.

Make Sense?

Formula to stop adding at a positive number
 

I just re-tested and mine fails if the 2nd number is a plus.

--
Don Guillett
SalesAid Software

"T. Valko" wrote in message
...
What about when/if the cash flow becomes 0? Is that considered positive?

-10 -5 -4 0 2 4

Try this. Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(B10,0,SUM(B1:INDEX(B1:G1,MATCH(TRUE,B1:G1=0, 0)-1)))

Biff

"nkhelp" wrote in message
...
I need help with the formula to stop adding when the cell number becomes a
positive number. I am looking at different senarios for a breakeven
point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change
another
calculation.

Make Sense?

Formula to stop adding at a positive number
 

I suppose it would be possible for this scenario:

-10 -5 -4 0 -2 -4

That's why I asked about 0 and use MATCH(TRUE,B1:G1=0,0)

Biff

"Don Guillett" wrote in message
...
I just re-tested and mine fails if the 2nd number is a plus.

--
Don Guillett
SalesAid Software

"T. Valko" wrote in message
...
What about when/if the cash flow becomes 0? Is that considered positive?

-10 -5 -4 0 2 4

Try this. Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(B10,0,SUM(B1:INDEX(B1:G1,MATCH(TRUE,B1:G1=0, 0)-1)))

Biff

"nkhelp" wrote in message
...
I need help with the formula to stop adding when the cell number becomes
a
positive number. I am looking at different senarios for a breakeven
point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change
another
calculation.

Make Sense?

Formula to stop adding at a positive number
 

confused, i may have read it on another way

I need A1 to add only through E1,
but the sum addition needs
to include cells F-G because
THEY may change to a negative number
when I change ANOTHER calculation.

then you may try either of the two..

=sum(A1:E1)+sumif(F1:G1,"<0") if either F or G is a negative to qualify in
summing with A:E

or

=sum(A1:E1)+if(and(F1<0,G1<0),F1+G1,0) if needs both F and G as negative in
order to qualify for summing with A:E...

regards
--
*****
birds of the same feather flock together..



"nkhelp" wrote:

I need help with the formula to stop adding when the cell number becomes a
positive number. I am looking at different senarios for a breakeven point.
I need one cell to add only numbers that are negative.

EXAMPLE
A B C D E F G
SUM -10 -5 -4 -1 2 4

I need A1 to add only through E1, but the sum addition needs to include
cells F-G because they may change to a negative number when I change another
calculation.

Make Sense?