Formula(ae) to mimic offsetinmg of losses against profits
 

Hi everyone,

Can anyone help wit hthe following please?

I have a cashflow where the first expressed in years where the first
few years are negative (aloss) and the next ,say, 10 or so are
positive.

I need to offset the losses against the profits as follows:


-100 -100 +50 +50 +100

in the flow above the -200 (-100+-100) is offset against the 50, 50,
and 100 hence makind the flow effectively zero.

This is easy to do manually but where the size of the positive flows
changes and the number of negative years change it becomes (i think )
almost impossible to automate.

I tried subtracting a running total but where the first positive year
was greater than the total negatives it failed!

Also, where there is . say, only one negative year the runnning total
fails!

Does anyone have any ideas. Perhaps involvong several rows worth of
built up functions?



Many thanks in advance


Bill

Bill,

Not sure I really follow, but you could get separate amounts like so

=SUMIF(A:A,"<0")

=SUMIF(A:A,"0")

for negative and positive amounts

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bill" wrote in message
...
Hi everyone,

Can anyone help wit hthe following please?

I have a cashflow where the first expressed in years where the first
few years are negative (aloss) and the next ,say, 10 or so are
positive.

I need to offset the losses against the profits as follows:


-100 -100 +50 +50 +100

in the flow above the -200 (-100+-100) is offset against the 50, 50,
and 100 hence makind the flow effectively zero.

This is easy to do manually but where the size of the positive flows
changes and the number of negative years change it becomes (i think )
almost impossible to automate.

I tried subtracting a running total but where the first positive year
was greater than the total negatives it failed!

Also, where there is . say, only one negative year the runnning total
fails!

Does anyone have any ideas. Perhaps involvong several rows worth of
built up functions?



Many thanks in advance


Bill