Divide a quantity as whole number among multiple cells

[Remya Ramakrishnan]

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

[#2]

@JZJANIK 

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.

[#3]

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

[#4]

@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

[#5]

@Willy Lau or others - 

I’m after this exact formula however I have individual values that the first few columns can’t go over, as opposed to just being equally divided. 

e.g.  B2 = 21

C2 can’t be more than 9, C3 can’t be more than 5, C4 can then be whatever value is remaining. 

How could the formula be tweaked to accommodate this please? 

Worth adding - all I need is the simple formula from the initial messages in this thread. Nothing as complicated as the latter messages!

[#6]

@CMullan880205 

Please make a sample workbook with the input and the desired output available via OneDrive, Google Drive or similar.

[#7]

@Hans Vogelaar thanks for the quick response.

hopefully this explains it

it’s simple really - I’m just rubbish at Excel!

User Charging 

[#8]

@CMullan880205 

See the attached version.