Forum Discussion
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 ...
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 )
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 )- thank you!!! perfect!
- You're welcome. Have a great weekend!
