SOLVED

# attempting to build an adjusting running calculator

Copper Contributor

# 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?

5 Replies

# Re: attempting to build an adjusting running calculator

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

# Re: attempting to build an adjusting running calculator

that works!!

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

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: attempting to build an adjusting running calculator

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
)

# Re: attempting to build an adjusting running calculator

thank you!!! perfect!

# Re: attempting to build an adjusting running calculator

You're welcome. Have a great weekend!