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 works brilliantly, you are such a legend. You have changed my life in one formula. Thank you! JMB17
Kazz84 This is a post from well over a year ago and has been really helpful to me. I have a similar issue as the previous posters and I used your formula with the sequence which is: LET(n,10,k,SEQUENCE(,n), hrs,B7, v,A7- hrs*(k-1), rest, v*(v>0), IF(k<n,IF(rest>hrs,hrs,rest),rest)) But I want to figure out how to add one more constraint on the sequence. I need the total for each day to not exceed 7. How can I do this?
| Proj Hours | hrs | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7 | Day 8 | Day 9 | Day 10 | Total |
| 8.4 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0.4 | 0 | 8.4 |
| 7.7 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0.7 | 0 | 0 | 7.7 |
| 10.5 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.5 | 10.5 |
| 0.7 | 1 | 0.7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.7 |
| 9.1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0.1 | 9.1 |
| 11.2 | 2 | 2 | 2 | 2 | 2 | 2 | 1.2 | 0 | 0 | 0 | 0 | 11.2 |
| 21.7 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 0.7 | 0 | 0 | 21.7 |
| 0.7 | 1 | 0.7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.7 |
| 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Total | 10.4 | 9 | 9 | 9 | 9 | 8.2 | 7 | 4.4 | 2.4 | 1.6 | 70 | |
| Constraint | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | ||
| Question: | How do I "nest" the constraint in so that each day totals 7 |