Split Number Across Cells - Evenly

[Agbarker]

Hi all

Its stumped me this one,

Column A will have random numbers (4 Cells) - range would be between 1-1000
A1=5
A2 = 5
A3 = 56
A4 = 78

these need to be split out amongst the adjacent cells B1,C1,D1 and E1 evenly and so on for each row.

have attached images before and after

I am using this currently, =MIN(IF(COLUMN()=3,$B40,$B40-SUM($C40:OFFSET(C40,0,-1))), ROUNDUP($B40/(SUMPRODUCT(NOT($36:$36=0)*1)-2),0))

however its not splitting the numbers out quite how would want.

for example this is what i get

5 split out = 2 / 2 / 1 / 0

what I would like is

5 split out = 2 / 1 / 1 / 1

if that makes sense.

any help would be appreciated.

 

[Snakehips]

@Agbarker

Is this what you are wanting?

Book1
	A	B	C	D	E
1	Ammount	a1	a2	a3	a4
2	5	2	1	1	1
3	5	2	1	1	1
4	57	15	14	14	14
5	77	20	19	19	19
6	9	3	2	2	2
7	2	2	0	0	0
8	1000	250	250	250	250
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=A2-SUM(C2:E2)
C2:E8	C2	=INT($A2/4)

 

[JamesCanale]

Mr Excel Playground 3.xlsm
	A	B	C	D	E
1	4	1	1	1	1
2	5	2	1	1	1
3	56	14	14	14	14
4	78	21	19	19	19
Sheet28

Cell Formulas
Range	Formula
B1:B4	B1	=A1-SUM(C1:E1)
C1:E4	C1	=INT($A1/4)

The way you had 78 split up doesn't seem quite right to me.

 

[Agbarker]

@Snakehips @JamesCanale

Thank you very much for that - once again I seem to be over complicating things.

and yes @JamesCanale - the 78 was not added up correctly.

 

[Rick Rothstein]

If you want a single formula solution, then put this formula in cell B1 and copy it across to cell E1, then copy the range B1:E1 down to the bottom of your data...

=INT($A1/4)+MAX(0,MOD($A1,4)>=COLUMNS($B1:B1))