Subtracting outgoing amounts from multiple cells until exhausted.

[pickle]

Date Amount Remaining Deduction
01/09/2022 200000 200000 50000
25/09/2022 100000 100000 50000
28/09/2022 150000 150000 50000
28/09/2022 200000 200000 50000
01/10/2022 125000 125000 35000

Hi all,

I am looking to make subtractions from multiple cells while there is still a value in there. So as an example the 200000 amount would become exhausted by the first 4 x 50000 amounts and then the 35000 would be then subtracted from the next amount down the 100000 and so on. I would want to continue to add deductions and incoming amounts to the sheet as time progresses and for the sheet to not deduct already deducted amounts again as I would want to keep the history of the sheet ongoing. I want to make the subtractions from each individual cell rather than a running summary of amounts left. I need each remaining amount to be exhausted before moving on.

Hope that makes sense?

Any help appreciated.

[pickle]

I still have not managed to find a solution to this with me playing around, I take it this is actually much harder than I thought maybe?

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[HansDouwe]

Are you still using Excel 2007?

[pickle]

Hi, no I am using excel 365, I have attached sample sheet.

Many thanks.

[HansDouwe]

Please try in E8 and copy down:

Please Login or Register  to view this content.

[FlameRetired]

Hi, no I am using excel 365, I have attached sample sheet.

Many thanks.

Please update your profile to show this.

Thank you ahead of time.

[pickle]

Hi, thank you so much I believe this seems to have worked from testing so far. I now seem to have one final hurdle to get over which I will try to explain.

I have a cell reference that will keep changing over time depending on the ins and outs of the above sheet, an average calculation at the time. What I will have is a list of the outgoings and what I want at the side is this average but I need it static for every line if that makes sense?

So I make a deduction of 20,000 and the average at that time of deduction might be 1.15 which I want to keep alongside the deduction but after the deduction may have changed to 1.14. I have a formula recalculating all of these but I need the one on the line of each deduction to remain static so its just the number and not a forumula otherwise they are all going to keep changing on every line to the same thing as I go along.

Basically how do I convert a cell that changes constantly to another cell at the time that would remember that value as a static number? The average will change over time, I want to remember the value before the change each time a deduction alters the value.

[pickle]

Please try in E8 and copy down:

Please Login or Register  to view this content.

Also the above is looking to work great. Could you just break down exactly what this is doing here, although its doing the job I would like to learn and figure out what it is exactly doing. Thanks again for your help.

[AliGW]

Have a look at Evaluate Formula on the Formulas ribbon - it will help you to understand formulae like this.

[pickle]

Thanks for everyones help. I dont think there is any way to capture a static value from a changing formula from what I can see / read.