Forum Discussion
Solved
attempting to build an adjusting running calculator
I want to have running total for a set number of days (lets say 20) where there is a total (lets say 3000) that is divided amongst the 20 days (so 150 a day) but if a certain day exceeds or fails to meet the 150 excel will automatically adjust the remaining days to make up the difference or subtract from the the target of 150 accordingly...
make sense?
Yes, then the formula can be simplified a bit:
=LET( distributed, amount / target, exceptions, SUM(FILTER(actual, actual <> distributed, 0)), allocate, (amount - exceptions) / total_days, allocate )
5 Replies
Here's an allocation formula. I've attached a workbook so you can take it for a spin.
=LET( distributed, amount / target, exceptions, SUM(FILTER(actual, actual <> distributed, 0)), re_allocate, (amount - exceptions) / total_days, IF(actual <> distributed, actual, re_allocate) )that works!!
is there a way to have the new daily need just down to one cell instead of across all the days?
Yes, then the formula can be simplified a bit:
=LET( distributed, amount / target, exceptions, SUM(FILTER(actual, actual <> distributed, 0)), allocate, (amount - exceptions) / total_days, allocate )