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))
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.
- Hans, thank you very much for tweaking 🙂
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.