Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting losses against profits
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting losses against profits
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 "Bill" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tax- offsetting losses against profits | Excel Worksheet Functions | |||
Formula(ae) to mimic offsetinmg of losses against profits | Excel Worksheet Functions | |||
modeling offset of losses against profits | Excel Programming | |||
Tax- offsetting losses against profits | Excel Worksheet Functions | |||
Formula(ae) to mimic offsetinmg of losses against profits | Excel Worksheet Functions |