Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
Find value in array | Excel Worksheet Functions | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
What is the formula for adding a number of days to an existing dat | Excel Worksheet Functions |