Forum Discussion
Restricting cells from equaling above a value
Here's a further cleaning up of the last version. Made to be a bit more flexible. You can just enter any annual goal in cell A1 and have it apportioned out equally among the months.
Then enter the actual for each month as that actual is known; the subsequent months are adjusted so that the annual goal is shown as still achievable.
I've added a bit more documentation, including brief explanations of the two most significant formulas.
Essentially what I am trying to do is this this:
"I would like to track results for a quota for this year.
I have the year total which is 40,000. I would like to have someone be able to manually enter their numbers for each month that equal up to this 40,000 quota. They would then add their actuals.
I want the quota to change with the actuals.
So, let's say if the quota for
January is 3000
February is 4000
March is 2500
The actual for January is 2500, then the deficit of 500 should make February Quota go from 4000 to 4500.
If the Actuals for February is 5500, then the surplus should bleed over into March, and so on all the way down, if that makes sense.
This is a bit different then my question but I have been trying to find different ways to explain it on various forums. Cells B4:B6 are Q1 Cells B9:B11 are Q2 and so on. These are the quotas. The cells to the right would be the actuals. C4:C6
So the total quota for the year is 40,000. I would write out my quotas, starting with 3,000 for January. The actuals for January end up being 2500. This gives 500 I need to make up for somewhere along the line in my cells to eventually still get 40,000. I am wondering if it is possible to trickle these values all the way down separating into cells and still end up with that 40,000 total at the end for the quota. If that makes sense
I've attached one way to do this. It could be made more streamlined (the calculations done in Column F (adjustment needed) could be incorporated into the calculations in Column C (adjusted quota), but I've separated them in part to make clearer the process.
Because I've used the LET function, you will need to have Excel 2021 or newer in order for this to work. If you don't have that capability, let me know and I'll break it into several steps. Here's a reference that explains the LET function. And I'd be happy to answer any questions.
- Hey man this is amazing! This is just what I am looking for. Is there also a way to make it divide out evenly among the months? So if you were +1000 for January that surplus would not be necessary for the remaining months instead of just 1? if not, it is okay. I appreciate it sm!
Here's a further cleaning up of the last version. Made to be a bit more flexible. You can just enter any annual goal in cell A1 and have it apportioned out equally among the months.
Then enter the actual for each month as that actual is known; the subsequent months are adjusted so that the annual goal is shown as still achievable.
I've added a bit more documentation, including brief explanations of the two most significant formulas.