Collins1805
New Member
- Joined
- Jan 2, 2014
- Messages
- 1
Excel 2010
I am trying to create a goal sheet for my sales people. I have an area that is populating 52 cells using VLOOKUP from another sheet. It is pulling their sales from each week last year. I then added at the end of each VLOOKUP formula *1.45 if I wanted a 45% increase etc.
So now that I have my goals I copied those values to another "goal table" (on the same sheet) to avoid dealing with all of the VLOOKUP formuals and I could just hide those.
Then I made another table for the sales people to enter their 2014 weekly numbers (on the same sheet).
So here is my question: I am trying to figure out a formual that adjust the goals based on their 2014 input that keeps them on pace. Example: If Week 1 of 2014 my goal is 10K and I did 61K I want a formual that will take the difference (51K) divide it by the remaining weeks (51) which in this example is 1,000 and distribute it to all of the remaining weeks cells so each week is 1,000 less because I over shot my goal week 1. And I also want it to do the opposite and add $$$ if I under shot my goal to keep me on pace.
So this is what I came up with that kind of works: =COUNTIF(B42,"<>B4")*(((B4-B42)/51)+B5)
My 1st weeks goal is B4 ($449.47)
My 2014 first weeks input is B42 (week 52 green)
B5 = Week 2's goal that i want adjusted to keep pace. ($5229.76)
Below is a snapshot of what I am talking about.
<tbody>
</tbody>
The reason why my formual only kind of works is because when I input a value in B42 (Week 52 green) that does everything I want it to. But if I leave B42 "0.00" it adjust the goal from its original value ($5,238.57 (orange) instead of $5,229.76 (yellow))
the bigger problem I am having is figuring out how to adjust multiple cells to keep my pace. So going back to my orginal example I want each cell to go down $1000 if that is the avg that I over shot my goal for the next 51 weeks. And then I need to duplicate that for the next cell to adjust the next 50 weeks and then the next cell to adjust the next 49 weeks and so on.
I appreciate any insite I can get on this. I know this was extremely long but I wanted to give as much detail as possible. I ahve been working on this for the past 3 days and can't figure it out.
any suggestions?
I am trying to create a goal sheet for my sales people. I have an area that is populating 52 cells using VLOOKUP from another sheet. It is pulling their sales from each week last year. I then added at the end of each VLOOKUP formula *1.45 if I wanted a 45% increase etc.
So now that I have my goals I copied those values to another "goal table" (on the same sheet) to avoid dealing with all of the VLOOKUP formuals and I could just hide those.
Then I made another table for the sales people to enter their 2014 weekly numbers (on the same sheet).
So here is my question: I am trying to figure out a formual that adjust the goals based on their 2014 input that keeps them on pace. Example: If Week 1 of 2014 my goal is 10K and I did 61K I want a formual that will take the difference (51K) divide it by the remaining weeks (51) which in this example is 1,000 and distribute it to all of the remaining weeks cells so each week is 1,000 less because I over shot my goal week 1. And I also want it to do the opposite and add $$$ if I under shot my goal to keep me on pace.
So this is what I came up with that kind of works: =COUNTIF(B42,"<>B4")*(((B4-B42)/51)+B5)
My 1st weeks goal is B4 ($449.47)
My 2014 first weeks input is B42 (week 52 green)
B5 = Week 2's goal that i want adjusted to keep pace. ($5229.76)
Below is a snapshot of what I am talking about.
| 2421 | ||
| Period 1 | $10,132.86 | |
| Week 52 | $449.47 | |
| Week 1 | $5,229.76 | |
| Week 2 | $4,407.25 | |
| Week 3 | $46.39 | |
| Period 6 | $147,210.99 | |
| Week 20 | $52,318.83 | |
| Week 21 | $56,533.69 | |
| Week 22 | $21,108.14 | |
| Week 23 | $17,250.33 | |
| Period 11 | $15,002.89 | |
| Week 40 | $2,986.19 | |
| Week 41 | $11,454.19 | |
| Week 42 | $0.00 | |
| Week 43 | $562.51 | |
| Period 1 | $10,132.86 | |
| Week 52 | $449.47 | |
| Week 1 | $5,238.57 | |
| Week 2 | $4,416.06 | |
| Week 3 | $55.20 | |
| Period 6 | $147,210.99 | |
| Week 20 | ||
| Week 21 | ||
| Week 22 | ||
| Week 23 | ||
| Period 11 | $15,002.89 | |
| Week 40 | ||
| Week 41 | ||
| Week 42 | ||
| Week 43 | ||
| Period 1 | ||
| Goal | $10,132.86 | |
| Remaining | $10,132.86 | |
| Week 52 | $0.00 | |
| Week 1 | $0.00 | |
| Week 2 | $0.00 | |
| Week 3 | $0.00 | |
| Period 6 | ||
| Goal | $147,210.99 | |
| Remaining | $147,210.99 | |
| Week 20 | ||
| Week 21 | ||
| Week 22 | ||
| Week 23 | ||
| Period 11 | ||
| Goal | $15,002.89 | |
| Remaining | $15,002.89 | |
| Week 40 | ||
| Week 41 | ||
| Week 42 | ||
| Week 43 |
<tbody>
</tbody>
The reason why my formual only kind of works is because when I input a value in B42 (Week 52 green) that does everything I want it to. But if I leave B42 "0.00" it adjust the goal from its original value ($5,238.57 (orange) instead of $5,229.76 (yellow))
the bigger problem I am having is figuring out how to adjust multiple cells to keep my pace. So going back to my orginal example I want each cell to go down $1000 if that is the avg that I over shot my goal for the next 51 weeks. And then I need to duplicate that for the next cell to adjust the next 50 weeks and then the next cell to adjust the next 49 weeks and so on.
I appreciate any insite I can get on this. I know this was extremely long but I wanted to give as much detail as possible. I ahve been working on this for the past 3 days and can't figure it out.
any suggestions?
