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))
This is what I guess you want
=ROUNDUP(IF(B2=$B2,$B2,$B2-SUM($C2:OFFSET(C2,0,-1)))/(SUMPRODUCT(NOT($1:$1=0)*1)-COLUMN(B2)),0)
Assume that you enter the formula at C2, and copy & paste the formula to the cells you need it. If later on, you want E, F, G etc., you just need to add the headers and paste the formula.
Eg:
| Category | Income | A | B | C | D |
| something | 6 | 2 | 2 | 1 | 1 |
| another thing | 5 | 2 | 1 | 1 | 1 |
- Willy,
It’s a while since you posted this solution - I’ve successfully used your formula, but would there be a way of adjusting it to set a maximum permissible value? I.e. in your example A, B, C etc could be no greater than 8.
Cheers Thank you Willy for the response, Sorry if I didn't articulate my problem clearly;
The cells in which I require values are C2,D2,E2,F2
its not required to have values in all cells, if we take 5 as example:
C2 has to take 2
D2 has to take 2
E2 has to take 1
D2 can be blank
