Formula regarding daily goal calculation needed

LaserDance · 08-10-2010, 11:11 AM

This is really a tricky and tough task.
I need a formula which calculates the daily goal of performance. For instance, there are 22 working days in a month. The monthly goal is $22 000. That means $1000 daily goal. The actuals however are different every day. For instance, if the actual for August 1st is $900, then for August 2nd we have a change in the daily goal: ($22000 - $900)/21 = $1004.76.
For August 2nd we have $1300 actuals, then the goal for August 3rd will be ($22000 - $900 - 1300)/20 = $990.

You catch the logic, but I can not make a formula and just paste it special in the other cells. Any help ?

**Whizbang** · 08-10-2010, 11:23 AM

Please see the attached. Is this what you were hoping for?

**DonkeyOte** · 08-10-2010, 11:29 AM

whizbang, using your sample - shouldn't it be along the lines of:

B4: =IF($C3="",$B3,($B$1-SUM($C$3:$C3))/COUNT($A4:$A$25))
copied down

ie SUM of Actual + Remaining Goal = B1

LaserDance · 08-10-2010, 11:29 AM

Exactly ! How come you were so quick with that? Call me in the weekend to go and have a beer. Thanks, dude !

**Whizbang** · 08-10-2010, 11:34 AM

Yes, DonkeyOte, that makes sense. Thank you.

I updated the attached. I also added formulas at the bottom to give the sum, the percent of goal and the percent of pace.

**jcotton79** · 08-31-2010, 01:11 AM

I have a similar issue but a little more complex I believe because I am trying to use a different sheet for each day. Please see attached and what I have already tried.

Originally Posted by LaserDance

This is really a tricky and tough task.
I need a formula which calculates the daily goal of performance. For instance, there are 22 working days in a month. The monthly goal is $22 000. That means $1000 daily goal. The actuals however are different every day. For instance, if the actual for August 1st is $900, then for August 2nd we have a change in the daily goal: ($22000 - $900)/21 = $1004.76.
For August 2nd we have $1300 actuals, then the goal for August 3rd will be ($22000 - $900 - 1300)/20 = $990.

You catch the logic, but I can not make a formula and just paste it special in the other cells. Any help ?

**jasonschafer** · 01-20-2015, 09:22 AM

Hi Whizbang is there any chance you could set up a formula very similar to this one for me ?

thanks

Originally Posted by Whizbang

Please see the attached. Is this what you were hoping for?

**protonLeah** · 01-20-2015, 07:46 PM

jasonschafer,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.