Averaging MTD

[stevi.d]

Sir, need your support, may be l might not explain it properly but the facts are

1. I need a formula in which l can take the next remaining days of the month which would be Zero to have the average to 50% only.

Example: I have remaining 15 Days in Feb and manually having 42.5% for 15 days to keep the average of 50% till month end in comparison passed day reflecting %-Balance

Attached is the complete sheet for reference. Regards, Steve

 

[Hui]

Stevi.d

Firstly, Welcome to the Chandoo.org Forums

Thankyou for posting here

Solution

1. The total of the 24 days is required to be 50%
That is the total of 24 days * 50% = 12

AH6: =SUMPRODUCT(--(WEEKDAY($D$1:$AE$1,1)<>5))*$AN$2

2. The Total so far is the 9 days * 64.15% = 5.77
AH7: =COUNT(D2:G2,I2:N2,P2:U2,W2:AB2,AD2:AE2)*AF2

3. The Total remaining is Total - Total So Far
AH8: =AH6-AH7
= 12-5.77
=6.23

4. The 6.23 has to be achieved over the 15 remaining days
that is 6.23 / 15 days
= AH8/(SUMPRODUCT(--(WEEKDAY(D1:AE1,1)<>5))-COUNT(D2:G2,I2:N2,P2:U2,W2:AB2,AD2:AE2))
=42%

or see the attached file

Please note that I do not answer posts to questions on my social media sites in any circumstances

.

 

[stevi.d]

Thanks the issue has been resolved... but yet there is a little change.. as per calculation the percentage is resolved but l have forget to mention how much the amount would be required as per daily % average... file is already attachced Sir but still if you required please do let me know thanks

 

[Hui]

I have no idea what your asking in the above ?
The calculations above is the average to be achieved every day to get the monthly average down to 50%

 

[stevi.d]

Thanks for your prompt reply firstly... May God always Bless You... my point is very simple here Sir, I have got the average exactly how you guided me.. and according to calculation that to get that average it is compulsory for the Franchise to deposit the amount to bring it... and that amount for that current day is required to be calculated... I am here attaching the file whereas you can find what exactly is my requirement as I highlighted as Daily Amount Deposit... I have highlighted the same amount @12600 to bring the reseller on the daily average but could get it in a calculated way...

 

[Hui]

I still have absolutely no idea what all that means
Even the 12600 you reference above is not in the file?
There is no Highlighted Daily Amount Deposit ?

 

[stevi.d]

Yes you are right.. am sorry.. coz l haven't sent you the complete file.. as the daily avg comes out out daily balances and l haven't sent you the balance column.. am here attaching the same balances from where these % have been out.. and am sure then you would be understanding it.. here am sending the file where you can find the balances from where we are taking the daily average you can find this in Example Sheet in the excel

 

[Hui]

So you need an average of 38.6% over the remaining 12 days to achieve 50%
What does the 12600 have to do with that ?

 

[stevi.d]

that would be the deposit amount which the Franchise require to deposit that day to have the given percentage 38.6% if they dont deposit it they would not come down with there balances

 

[stevi.d]

Extremely Sorry for bothering you brother but the key logic has been dramatically changed... the solution which you provided me last has been perfect and thank you as l improved in it and finally found out the daily deposit is required... but a Question

Question: The Total Average of Past 10 Days is 64.15% and the Security Deposit is 100,000/-.... what amount should be deposit on the 11th Day to bring the Avg of 10 Days 64.15% to 50.00%

File Attached:

Hoping for a prompt reply.

Regards,
Steve

 

[Hui]

If 100,000 is 64.15% over 10 days that is 6.415 Total over 10 days

to get 50% over 11 days you need a total of 5.5

So there is no way you can get a 50% average as you already have to much to achieve 50% in 11 days

The minimum days you need to get back to 50% is 12.83 (13 days)
6.415/12 = 53.45%
6.415/13 = 49.34%

I have no idea what the relationship between the Daily percentages and the 100,000 is because you have never told me

 

[stevi.d]

The Daily Percentage is there daily working where there outstanding is over percentage....

And 100,000 is there security deposit where they have deposited and need to work in the provided amount sort of credit provided by the company... but that is not much important

Here is the updated sheet whereas after past 14 working days the AVG comes upto 58.25%, now I need to know what amount exactly they should deposit to bring there 58.25% to 50.00% where the deposited security is 1000,00 and as on Manual Calculation they have to Deposit 115800 on the current 14th Day to get the average to 50%... Sir...Sheets Attached: Master Sheet and Example Sheet