SOLVED

# Divide a quantity as whole number among multiple cells

Copper Contributor

# 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 columns, 2.75 will result in the 4 cells

But I need the excel formula to get the whole number, next integer(3 for 2.75) in all cells.

Any response will be helpful. Thanks

62 Replies

# Re: Divide a quantity as whole number among multiple cells

Like this, for example:

The formula in B4 is

``````=LET(
total, B1,
unit, B2,
num_1, QUOTIENT(total, unit),
num_2, num_1+1,
evenly, ROUND(MOD(total, unit), 4)=0,
seq_1, SEQUENCE(num_1, , unit, 0),
seq_2, SEQUENCE(num_2),
rest, (total-(num_1-1)*unit)/2,
seq_3, IF(seq_2<num_1, unit, rest),
IF(evenly, seq_1, seq_3)
)``````

See the attached sample workbook.

# Re: Divide a quantity as whole number among multiple cells

Hello,

Works like a charm, thank you kindly :) though I had to walk around it through opening file in browser version of Excel, implanting it there and then when I opened file in excel app it looks that it does work indeed :)

Once again thanks for helping out, i wouldn't figure it out alone that's for sure!

All the best :)
Jakub

# Re: Divide a quantity as whole number among multiple cells

@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