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))
I've been looking all over the internet trying to find a solution to my problem and this post seems to be on the right track but does not quite get to the necessary answer I am looking for.
I need to divide a whole number into 5 columns with a maximum of 3 in each column apart from column 5 which can be the remaining number of whole numbers,
Example: The whole number is 20
Column A 3
Column B 3
Column C 3
Column D 3
Column E 8
Is there a formula that can help me achieve this?
I would be sincerely grateful for any assistance.
If your target number (20) is in cell A1, then try this in cell A2 and copy to cell E2:
=(COLUMNS($A2:A2)<5)*MEDIAN(0,3,$A$1-(COLUMNS($A2:A2)-1)*3)+(COLUMNS($A2:A2)=5)*MAX(0,$A$1-12)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
