Offsetting losses against profits

[Bill]

All,

I am looking to create code that models the treatment of losses for
tax purposes.

Essentailly this means that I have a range of cells wit hvalues ,say,

-100, -70, 50, 50, 50, 50, 50, etc

THe first negative values in the series are added together and then
taken from the succeeding positive values to return zero or whatvever
is left. The results are returned in the row below.

In the above case the row below would read :

0,0,0,20, 50

so the -170 was spread over the 50, 50, 50, 50, 50.


HOw can I create a construct that has a running sun, so to speak and
can retuen the above.

I tried with formulae but could not get it to work when the negative
values wer eintersperced throughout the series.

I imaging it willl have to be an if....then construct.


Any ideas

Many thanks

 

[Bill Kuunders]

Bill

assuming your numbers start in A1 and across to say L1 (for 12 months)

enter in A2 =if(A1<0,0,A1)

and enter in B2 =IF(SUM($A1:B1)<0,0,SUM($A1:B1))

{this can be translated in enghlish... If sum a1 + b1 is negative show a
zero otherwise show the sum a1 + b1}

now click onto the bottom right corner of B2 when the mousepointer changed
to " + " hold and drag accros to L2

The result will be 0 0 0 0 0 30 80 130 and not 0 0
0 20 50 and?
After the -100 and -70 it takes four lots of 50 before you are in positive
mode not three..

Hope this was what you planned to show.
greetings from New Zealand

Bill K