Help with forumla to calculate daily targets

**Climaxgp** · 04-09-2009, 07:05 AM

Hi Guys

I need a forumala to calculate the daily target left.

Example: the monthly target is 2000 sales, broken down daily assuming there is 20 working days, that will be 100 sales per day. Assuming that the daily sales target of 100 sales is not met i need a formula that will tell me how many sales are required per day that we need to do to make up the 2000 sales. Also bear in mind that the number of working days are decreasing

**DonkeyOte** · 04-09-2009, 07:11 AM

It may be easier if you provide a sample of how your data is laid out - ie where the daily actuals are stored etc...

In calc terms your required daily sales will be:

(Target-Actuals to Date)/Days Remaining

**Climaxgp** · 04-09-2009, 07:37 AM

Thanks, i have attached the example. Also, just remember that the daily target if not achieved needs to be added on to the following daily sales targets

**DonkeyOte** · 04-09-2009, 07:54 AM

Using the previously mentioned formula

H6: =(H2-SUM(D2:D27))/(H3-COUNT(D2:D27))

Assumes that if a day has no sales a 0 is entered, ie not left blank.

**Climaxgp** · 04-09-2009, 08:48 AM

Thanks for the info buddy! Also just wanted to find out is there a way that excel can autmatically reduce the number of days from a given number? Example if i say there are 20 working days in a month can excel reduce the working days automatically as the days progress?

**DonkeyOte** · 04-09-2009, 08:57 AM

Isn't that what this does ?

(H3-COUNT(D2:D27))

The above (which is the divisor in your required rate calc) assumes that for each working day a numerical value is entered into the appropriate row in column D ... thus as you add values to D so the remaining working days will adjust automatically given they are calculated by means of subtracting the number of values entered at that point in time in Column D from the total working days as specified in H3.

I'm not entirely sure I'm following so please elaborate as much as possible if you need more info.