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.
I think we need a bit more complete and precise definition of the task at hand. It would also help to get the bigger still picture, i.e., what is the context into which this is fitting?
But just to address what you have written so far, there are some ambiguities that need to be resolved.
B4:B6
B9:B11
B14:B16
B19:B21
I want to be able to enter values into B4 going down
Do you expect values only in those ranges specified, or in B4 all the way "going down" to B23, ending just before B24? Presumably the former, but it's not totally clear.
...and the remaining values will populate in the cells. I want the cells to populate based on the total value of B:24 which is 40,000
Presumably you want numbers/values in each of these various cells, but are there parameters to be complied with? Or would it be acceptable if half of them (a random assortment) contained zero? Can they be totally random? Should each fall within a Min to Max range?
So if I wrote 3400 in B4 then the other B cells would write values equaling up to 40,000, and change as I write them
- "Equalling up to 40,000" could be read with the emphasis on the "up to" -- meaning the total could be any number up to or equal to 40,000.
- I suspect, however, that what you mean is "totalling 40,000", i.e., no "up to" involved. The set of numbers would have to adjust themselves so that the sum of them always equalled 40,000.
- Are fractional values (values to one or two decimal places) acceptable, or do you want the values to be whole numbers?
- When you say "change as I write them" you are introducing a paradoxical wrinkle: the cells will need to contain a formula of some kind (at least a good subsection of them should) and overwriting those formulas will introduce limitations. Or are you going to limit your writing to cell B4?
As noted at the start, a description of the context here would be helpful as well. What lies behind (or in front of) this request? What purpose is being served? [Not a spurious question: the objective is often key to what design is most suitable.]
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
- MUCH CLEARER, Thank you. That context makes the whole thing make sense. I'll work on it....in the full realization that others here may jump in with a solution before I can. I've some ideas.....that would involve three columns rather than just one, a column for Initial Quota, a column for Actual, and a column for Adjusted (for subsequent rows). Excel often offers multiple ways to get from Point A to Point B, so I'll be curious to see what others come up with.
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!