Forum Discussion
Divide a quantity as whole number among multiple cells
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
This works brilliantly, you are such a legend. You have changed my life in one formula. Thank you! JMB17
If with dynamic arrays
that could be
=LET(n,5,k,SEQUENCE(,n), maxv,3, v, MIN(ROUND(A1/n,0),maxv), rest, A1-v*(n-1), IF(k<n,v,rest))but perhaps we understood the requirement differently:
I am very impressed by your formula and it may be able to solved a problem that I faced.
I have a number (say 223 in A1) and I want to distribute it over a selected cells (say 20 rows from B1:B20) more or less evenly. The number in each cell must be a whole number and the sum of all rows must be exactly the same as A1. Is there a simple Excel formula to achieve this?
Please see my attached worksheet for details. Do note that the number of row is variable.
Thank you.
See the attached version. You can change the values in A1 and F1 to see the result change.
Wow, that’s fantastic. The formula works nicely by inputting number of rows into an additional cell.
I have an additional question. In a real life worksheet with lots of data and without a blank column to key in number of rows due to worksheet protection, is there anyway to tweet the formula such that it knows how to count a range of selected rows? You can try to imagine that after row 20, there is another number (say A21=333) and it needs to be redistributed to 15 rows (say B21 to B35). Hence, I need a flexible formula that can take care of any number of rows.
Thanks a lot for your help. It is really enlightening to learn from you.
SergeiBaklan I tried the let formula but i dont seem to be able to use it. My requirement is like that but I ant to split the numbers to pieces of 8. (because for me, i need to split total manhours to 8 man hours / day/column)
Do you mean something like this?
=LET(n,5,k,SEQUENCE(,n), hrs,8, v, A1-hrs*(k-1), rest, v*(v>0), IF(k<n,IF(rest>hrs,hrs,rest),rest))Good Morning,
I am trying to do a similar thing with this formula with a bit of a difference with how I require the result to be generated. For example I would like to divide a whole number across multiple cells but using a maximum value (maybe referencing another column as this number could change between each row) and the last cell to calculate the remaining number if its below the maximum. See below representation:
*Note: is there a formula to leave a blank in remaining cells as opposed to zero?
Units Capacity Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 Day 8 Day 9 10 2 2 2 2 2 2 10 3 3 3 3 1 10 4 4 4 2 20 3 3 3 3 3 3 3 2
