Forum Discussion
Solved
Distribution of 1 cell to many cells
I am looking for a formula to use for accounting purposes. In one cell I want to input a number x. Then in subsequent cells I want that number to be distributed, in order, for the subsequent cells with parameters previously set. For example, cell 1 max is 400, cell 2 max is 200, then cell 3 max is 600. I want to input say 1000 in the formula cell and that 1000 be distributed, in order to cell 1 first until the max is reached, then spill over to cell 2 until max is reached, then spill over to cell 3. So, 1000 is entered into the first cell and automatically cell 1 would show 400, cell 2 would show 200, and cell 3 would show. 400.
Is there a formula capable of performing this task?
You can use the following formula to achieve the distribution of a given number across multiple cells with predefined limits:
=MIN(x, limit1) in cell 1
=MIN(MAX(0, x-limit1), limit2) in cell 2
=MAX(0, x-limit1-limit2) in cell 3
Where x is the total number you want to distribute, limit1 is the maximum limit for cell 1, limit2 is the maximum limit for cell 2, and cell 3 is where any remaining amount will be displayed.In your example, if you input 1000 in the formula cell, then cell 1 will show 400, cell 2 will show 200, and cell 3 will show 400.
Note that if the sum of the maximum limits is less than the total amount you want to distribute, then the formula will not be able to distribute the entire amount and the last cell will show a negative number.
3 Replies
You can use the following formula to achieve the distribution of a given number across multiple cells with predefined limits:
=MIN(x, limit1) in cell 1
=MIN(MAX(0, x-limit1), limit2) in cell 2
=MAX(0, x-limit1-limit2) in cell 3
Where x is the total number you want to distribute, limit1 is the maximum limit for cell 1, limit2 is the maximum limit for cell 2, and cell 3 is where any remaining amount will be displayed.In your example, if you input 1000 in the formula cell, then cell 1 will show 400, cell 2 will show 200, and cell 3 will show 400.
Note that if the sum of the maximum limits is less than the total amount you want to distribute, then the formula will not be able to distribute the entire amount and the last cell will show a negative number.
- Can you put a screenshot of your input then desired output?
