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))
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.
A formula doesn't "know" which range you selected. To do what you want would probably require VBA code, but that seems overkill.
Without VBA, see the attached version. I moved the cell with the number of rows to column A.
- Hans, thank you very much for tweaking 🙂