Forum Discussion
Solved
Divide a quantity as whole number among multiple cells
Hello,
Any suggestion for an excel formula to derive A2, B2, C2, D2 values.
Eg:
Category
Income
A
B
C
D
Sal1
11
3
3
3
2
if we divide 11 among 4 c...
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
Remya,
your cell references (A2, B2, ...) can't be correct.
I assume "Category" is in A1 and "2" is in F2.
=IF(COLUMN()=6,$B2-SUM($C2:$E2),ROUNDUP($B2/4,0))
Hello Detlef,
Sorry I didn't explain it right!
I look for auto population of values in C2,D2,E2 and F2 cells.
and if we the quantity to divide is 5,
I need to get
C2=2
D2=2
E2=1
F2=0(blank)
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))
Willy Lau or others -
I’m after this exact formula however I have individual values that the first few columns can’t go over, as opposed to just being equally divided.
e.g. B2 = 21C2 can’t be more than 9, C3 can’t be more than 5, C4 can then be whatever value is remaining.
How could the formula be tweaked to accommodate this please?
Worth adding - all I need is the simple formula from the initial messages in this thread. Nothing as complicated as the latter messages!
Please make a sample workbook with the input and the desired output available via OneDrive, Google Drive or similar.
- I have a similar issue. I want to spread a base number over 7 cells to the right and the formula above works fine. My additional issue is that I want to repeat the process every 7 cells. Ex. 17 units to be rented each week. the above formula gives 3, 3, 3, 3, 3, 2, 0 (Mon - Sun) Then the following Mon it repeats using the same base of 17. Thanks in advance.
