Countifs with weekday and evenly distribute to working days

[hudson andrew]

Hi all ,

Could someone advise me please on the below example .

I was trying to evenly distribute all my Total count of volume (10,000) to all weekdays .for example let’s take current month 21 working days and my equal allocation is (10,000/21 days )= 476 . now on day 1 i.e 10/03/2016 I might not be able to complete my 476 allocation but ended up with 250 . my outstanding would be 476-250=226 . now my total unknown to work on from Day 2 to Day 29 would be {476+(226/20)=487.3} likewise so on .

However I have a function that could only helps me with equal distribution.

I unable to do second part .

attached is the sample file for your reference .

Please its little urgent if someone could get back on this ..

[JohnTopley]

Calculation is

(10000-SUM(allocation to date))/number of days remaining

so at Day 2 :

(10000-476)/20 = 488 for remaining 20 days

If allocation on Day 2 is 400 then new allocation

(10000-876)/19 =480

?????

[hudson andrew]

John,

First of all thanks for coming back on my request .

You are up to the mark .. but I want that evenly distributed automatically for remaining day .

As I have presented in my example.

Let me know if you have questions on it.

[Ash_Maverick]

Is this correct ?

[hudson andrew]

Hi Ash,

Thanks for your assistance, this is basically a template I am preparing .

However , above formula does not meet my requirement . because my Unknown to work should be evenly allotted .
if you see the file it has different brake up .

Thanks again.

[JohnTopley]

You cannot get the work evenly distributed because as the month progresses the work per day will continually change as per Ash's solution.

See the attached which shows equal allocation for future dates.

[hudson andrew]

John- I agree with you but. on 3rd day again I left with few more deductions . even that should be evenly distributed for remaining days .

thanks again for your help .

[hudson andrew]

thanks guys for your help .. we can ignore this discussion for some time because .. there are lot many things to be considered for this..

[JohnTopley]

The solutions (both Ash's and mine - same really) do distribute evenly over any remaining days as I explained in an earlier post.