Forum Discussion
Distribution of 1 cell to many cells
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.
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.
