Combine negative number with the number in cell next to it until the sum becomes positive

**iniakupake01** · 06-05-2017, 08:28 AM

I need to have a formula that i can put in each cell in row 2 below, that will return the value in the cell above it - if the number above it (row 1) is positive.
However, if the cell above it (row1) is negative, I need to return 0 in cell row 2, and cumulate-sum the negative number in row 1 with number next to it and so on, until I get a positive amount from the temporary sum and display this in row 2.

In short, cells in row 2 cannot be negative, but the total in row 2 should still be = total in row 1.

Row 1: 0 2 -3 2 5 -2 3
Row 2: 0 2 0 0 4 0 1

was thinking an array formula, but not sure how.

**Special-K** · 06-05-2017, 08:34 AM

in A2
=MAX(A1,0)

will give you a positive number of the row above or 0 if its negative

"cumulate-sum"

Not n English word so i dont understand what your after from this point.

**Monimonika** · 06-05-2017, 03:05 PM

Nevermind, need to redo formula!

**Jonmo1** · 06-05-2017, 03:25 PM

Maybe this in A2 and filled right

=MAX(0,SUM($A1:A1))

**Monimonika** · 06-05-2017, 03:38 PM

Back with one that I think works:

NOTE: You will need to have start from column B or later. Not sure how to fix so that REF!# errors don't propagate down if pasted into column A and formula is dragged across (since there's no column left of A).

=IF(COLUMN(B1)=2,B1,MAX(SUM(A1:$B1)-SUM(A2:$B2)+B1,0))

When dragged across, column D formula becomes:

=IF(COLUMN(D1)=2,D1,MAX(SUM($B1:C1)-SUM($B2:C2)+D1,0))

**iniakupake01** · 06-05-2017, 09:02 PM

Thank you Monimonika and Jonmo and Special K,
the last formula from Monimonika worked for the sample given. However when I changed the combination such as below, it didnt return result in
row 2 correctly

0 -1 0 0 0 2 3 data
0 -1 0 0 0 2 3 result using monika formula
0 -1 0 0 0 1 4 expected result

The 7th column is 1 because -1+0+0+0+2 = 1.

I guess it will just require a bit more tweaking of the current proposed formula.

for special K, cumulate -sum i meant as cumulative sum. Basically I need to group the cells in row 1 together with the next additional columns, until my cumulative sum starting from where the column shows negative amount becomes a positive amount when combined with the cell(s) to its right and display that positive amount in the last column of the cumulative columns.

**iniakupake01** · 06-05-2017, 09:04 PM

ow n i don't mind adding new columns/rows/ use additional helper rows/columns if that solves the problem.
Thanks.

**BoredWorker** · 06-06-2017, 05:54 AM

Duplicated post

**BoredWorker** · 06-06-2017, 05:55 AM

0 -1 0 0 0 1 4 expected result

Hi iniakupake01,

Can I know why the 2nd result is -1?

and,
I believe Jonmo1 formula is good enough.

Can you use the Jonmo1's formula?

**Jonmo1** · 06-06-2017, 08:52 AM

I don't understand how you got your expected results in your first example.
But this definately provides the expected results of your 2nd example
=MAX(0,SUM($A1:A1))

**ChemistB** · 06-06-2017, 09:22 AM

With the exception of the -1 in the 2nd position in example 2 (I thought negative values were brought to 0?) this formula works for both examples

=MAX(0,SUM(INDEX($A$1:A$1,IFERROR(LOOKUP(2,1/($A$1:A$1<0),COLUMN($A$1:A$1)),1)):INDEX($A$1:A$1,COLUMN(A$1))))

I believe that the OP is restarting the summation when a negative value is encountered. This accounts for both examples (except for that negative 1 which I think is an error)

**Monimonika** · 06-06-2017, 11:04 AM

Originally Posted by iniakupake01

However, if the cell above it (row1) is negative, I need to return 0 in cell row 2, and cumulate-sum the negative number in row 1 with number next to it and so on, until I get a positive amount from the temporary sum and display this in row 2.

0 -1 0 0 0 2 3 data
0 -1 0 0 0 2 3 result using monika formula
0 -1 0 0 0 1 4 expected result

The OP is contradicting themselves on what they want (why is a -1 expected in row 2???)
Also, my results are as follows:

0 -1 0 0 0 2 3 row 1 data
0 0 0 0 0 1 3 row 2 result

The first row of data should start at cell B1 for my formula to work.
There is NO WAY for my formula to result in a negative number, so the OP is pasting stuff wrong.

EDIT: I'm an idiot. I forgot to account for if B1 is negative, so of course a negative number is possible. Doh!
Corrected formula (might want to check on the formulas from others):

=IF(COLUMN(B1)=2,IF(B1>0,B1,0),MAX(SUM(A1:$B1)-SUM(A2:$B2)+B1,0))

Combine negative number with the number in cell next to it until the sum becomes positive

LinkBack

Thread Tools

Rate This Thread

Display

Combine negative number with the number in cell next to it until the sum becomes positive

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Re: Combine negative number with the number in cell next to it until the sum becomes posit

Thread Information

Users Browsing this Thread

Similar Threads

Formula with IF, can't get rid of #VALUE!, works for positive number not negative number.

Match Negative number with its corresponding positive number after matching columns

Adding negative number = positive in another cell.

Carrying Over Negative number to next column as a positive number

[SOLVED] When Cell is a negative/Positive number insert clipart next to it

[SOLVED] Display the contents of a cell only if it is a positive or negative number.

Move Negative Number and Change to a Positive Number

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions