Countdown

Hi, I've attached a sheet title book2
I need help creating a formula to countdown targets each week, my sales quarter is 13 weeks long, total target is divided by 13 to give a weekly target, I want the sheet to be able to include any shortfalls or overage into the remaining weeks targets over the quarter.

Many thanks

I am not sure if I understand your question fully. There are a number of questions about what to do with targets for weeks gone by and how do you intend to keep track of what week you are in etc...

Anyway, look at the example attached. I've written some formulas for Post. As you enter actual data for each week, remaining weeks will get the quota distributed. Once a week is gone, it's quota will re-adjust to the original number whichis total quartlerly quota divided by 13.

If that's not right, then you should fill in some sample data for a couple of rows and explain under different situations, what you would like ot see.

modytrane

Thanks for reply, I have added some examples with comment boxes to try and explain what I want.

Many thanks

That's exactly what my sample file does.
The only difference is that for the weeks gone by, the TGT amount reverts back to the original average. That's what should happen anyway. There is no point in keeping those target amounts to different values.
In the sample I sent you, first clear the values in ROW 6 and then enter values for Post in Row 6 under Wk 1, Wk 2 etc and see how the TGT amount changes. The formula will recalculate target amount for weeks remaining, based on total achieved up to that point. It distributes overage or shortfall over remaining weeks.


The only other question remaining is, how do you plan on keeping track of weeks? Do you want to do it manually or do you want to make it auomatic based on current date. You could enter a starting date somewhere and calculate Weeks Gone in Cell B1, based on today's date.
modytrane

It would be great if I could get it to work out the weeks automatically, However I wouldn't have a clue how to do it?? Thanks for your help so far.

Would you be able to help me with the command for changing the weeks automatically
Many thanks

see the attached file.
Cell B1 will calculate current week number within the quarter. It starts out with 1/1/2010 as week number one. Every thriteen weeks it will count again from 1 -13.
Its based on current date, so every time you run the program, it will look at today's date and calculate week number within this quarter.
If you want number of weeks gone by, then subtract one from that formula, as follows.

Please Login or Register  to view this content.

modytrane.

Hi, Thanks, It doesn't seem to be working, cell B1 is displaying #NAME?, do I need to enter the start date anywhere? Thanks again

Thanks alot for your help