Find negative value and apply to next positive value

[jamphan]

I have a series of accounts with values for 0-15 months in a row. Some of the months have a negative value. What I would like to do is first find the negative value, if any in the row and apply it to the first positive number in the row. The negative numbers always preced my positive numbers in the row. Some more info is the negative may be in month 0 and the next positive may be in month 10 or could be month 1. Any help? I'm stumped.

[Richard Buttrey]

Hi,

Can you upload an example. It's not immediately clear.
Did you really mean 'find the first negative value' or 'first find the negative value' - there's a world of difference.

What do you mean by 'apply it to the first positive number'? Add it? replace it?

Rgds

[jamphan]

Example:
0 1 2 3 4 5
-1 5 8

So I would want the -1 to be added to the 5 in month 3 but like I said the negative numbers and positive numbers are not always in the same place.

[jamphan]

The above reply example did not come out like I typed it. The negative number is supposed to be in Month 0 and the 5 is in month 3 and the 8 in month 5.

[Richard Buttrey]

Hi assuming your numbers

Please Login or Register  to view this content.

are in A1:I1, then use the following array formula. Don't forget with array formulae you need to commit them with CTRL SHIFT and Enter

Please Login or Register  to view this content.

If you've entered it correctly you'll get the squiggly brackets {} at either end. You don't need to enter the {} brackets - you just get them as a bonus

Rgds

[shg]

If I understand correctly, you could do it like this:

Please Login or Register  to view this content.

The formula in B2 and copied across is

=MAX(0, SUM($A2:B2) - SUM($A3:A3) )