attempting to build an adjusting running calculator

[frank1510]

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?

[#2]

@frank1510 

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)
)

[#3]

@Patrick2788 

that works!!

is there a way to have the new daily need just down to one cell instead of across all the days?

[#4 · Accepted solution]

@frank1510 

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]

thank you!!! perfect!

[#6]

You're welcome. Have a great weekend!