Formula to calculate daily sales needed to reach a specific target

Please post file to the forum as many members will not visit file-hosting sites.

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks a lot for the tips. i've attached the file here. and i hope it's clear enough for the request.

Hello,

thanks so much for your help. is it possible to make the daily needed numbers return results per day and not the total needed in each period. for example, what is the needed number of sales on one Friday?

Thanks.

Daily Sales

Sun-Thu

=ROUND($H2/COUNTIFS($C$2:$C$32,"",$D$2:$D$32,"<" &6),0)

Fri

=ROUND($I2/COUNTIFS($C$2:$C$32,"",$D$2:$D$32,6),0)

Sat

=ROUND($J2/COUNTIFS($C$2:$C$32,"",$D$2:$D$32,7),0)

i maybe getting this the wrong way. The formula on daily sales cells H4,I4 and J4 is supposed to set a daily target for each day of the week till the end of month to reach the final target on cell F2.

so for example, if i take the numbers you shared in the above sheet and start applying them manually on all days. the final day or two of the month which are Fri 30 and Sat 31 predicted numbers by the formula on cells I4 and J4 should both equal the number on G2, right?

compare the attached sheet to what you shared to see what i mean. and again thanks a lot for your help in this

The calculations are correct: if you put the appropriate day figures in, then the month total (day 1 to day 31) is 109998 against a target of 110000!!!

yes, but how can the last two remaining days of the moth prediction not equal the total needed for the month on G2. this means that we will close the month behind. can you help me understand the formula better?

I have re-looked at your posting and now understand the point you make where the proportions get completely distorted. i am not sure how to handle this (I am no mathematician).

The formula simply takes the sales-to-date and subtracts this figure from the target to give the balance required.

I then calculate the sales-to-date for Sun-Thu, Fri and Saturday and use these figures to apportion the balance.

So for example, if Sun-Thu are 60% of sales then 60% of the balance is "allocated" to Sun-Thu. I then calculate the number of Sun-Thu days left to give a daily figure.

This is repeated for Fri and Sat.

When we get to situation where there is only (a few) 3 days left i.e Thu, Fri, Sat then this calculation fails.

The "standard" calculation is to apportion the data equally among ALL days then will not get this distortion.

So with 3 days left and a balance of 12507 , we would divide this by 3 giving 4169 for each day.

it is the "bias" to Sun_Thu which causes the problem.

Thank you so much sir for your efforts. would breaking down Sun-Thu to calculate each day separately, make it easier?

See the table in the attached where every is calculated on a per day basis: note to date you have only reached 20% of your target so reaching your target is extremely unlikely.

The calculations are the same as previously but on a daily basis.