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))
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 - You are welcome. I appreciate the kind words, but much of what I know came from people who are much better at this than me - I'm just the middleman.
If with dynamic arrays
that could be
=LET(n,5,k,SEQUENCE(,n), maxv,3, v, MIN(ROUND(A1/n,0),maxv), rest, A1-v*(n-1), IF(k<n,v,rest))but perhaps we understood the requirement differently:
I am very impressed by your formula and it may be able to solved a problem that I faced.
I have a number (say 223 in A1) and I want to distribute it over a selected cells (say 20 rows from B1:B20) more or less evenly. The number in each cell must be a whole number and the sum of all rows must be exactly the same as A1. Is there a simple Excel formula to achieve this?
Please see my attached worksheet for details. Do note that the number of row is variable.
Thank you.
See the attached version. You can change the values in A1 and F1 to see the result change.
SergeiBaklan I tried the let formula but i dont seem to be able to use it. My requirement is like that but I ant to split the numbers to pieces of 8. (because for me, i need to split total manhours to 8 man hours / day/column)
Do you mean something like this?
=LET(n,5,k,SEQUENCE(,n), hrs,8, v, A1-hrs*(k-1), rest, v*(v>0), IF(k<n,IF(rest>hrs,hrs,rest),rest))
