Subtract from one column first, then another if remainder

**haylleyjo** · 06-07-2013, 02:54 PM

Hi y'all --- I've run into another Excel puzzle. I have a single number that I need to subtract from a series of numbers in the same row, rolling the remainder left from the first number over to the next number until the first number has been entirely subtracted out of the number series.

Eg. Pre-calculation: columnA [] columnB columnC columnD
300 10 200 300

Post calculation:

columnA [] columnB columnC columnD
300 0 0 210

Column A's value can be left there or deleted post calculation - it is not important once the calculation is done. I'd ideally like to use a series of formulas to do this instead of writing a macro. I have to give this to someone who is not a macro user.

Any help/guidance/etc would be very welcome!

**FDibbins** · 06-07-2013, 05:07 PM

You cant change what's already in a cell, with a formula in that same cell - you can either have a formula or text/value, not both.

You could try this in the row below, copied across,,,
=IF(SUM($B$1:B1)<$A1,0,SUM($B$1:B1)-$A1)

**haylleyjo** · 06-11-2013, 11:56 AM

Thank you so much! This totally pointed me in the right direction. I did some more playing around with conditional equations and found a combination that worked for my purposes. In column B I put: =IF(SUM($B8:B8)>=$A$8,SUM($B8:B8)-$A$8,0) and then in column C I put: =IF(B13<>0,C8,IF(SUM($B8:C8)>=$A$8,SUM($B8:C8)-$A$8,0)).

**Rokshana Perveen** · 10-22-2018, 12:05 PM

hi,
I hv same kind of situation where i need to put the remainder if 1st one is not fulfill the demand then consider the 2nd one and keep on going until the remainder get 0. to get the 1st remainder i used the formula=IF(B13<0,0,B13-F13)here B13 is demand and F13 is 1st fillup unit.but cant proceed for the next step. looking for advise/suggestion to solve it.

**FDibbins** · 10-23-2018, 02:25 AM

Rokshana Perveen welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.