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]

This is what I guess you want

=ROUNDUP(IF(B2=$B2,$B2,$B2-SUM($C2:OFFSET(C2,0,-1)))/(SUMPRODUCT(NOT($1:$1=0)*1)-COLUMN(B2)),0)

Assume that you enter the formula at C2, and copy & paste the formula to the cells you need it.  If later on, you want E, F, G etc., you just need to add the headers and paste the formula.

Eg:

Category	Income	A	B	C	D
something	6	2	2	1	1
another thing	5	2	1	1	1

[#3]

Remya,

your cell references (A2, B2, ...) can't be correct.

I assume "Category" is in A1 and "2" is in F2.

=IF(COLUMN()=6,$B2-SUM($C2:$E2),ROUNDUP($B2/4,0))

[#4]

Thank you Willy for the response, Sorry if I didn't articulate my problem clearly;

The cells in which I require values are C2,D2,E2,F2

its not required to have values in all cells, if we take 5 as example:

C2 has to take 2

D2 has to take 2

E2 has to take 1

D2 can be blank

[#5]

Hello Detlef,

Sorry I didn't explain it right!

I look for auto population of values in C2,D2,E2 and F2 cells.

and if we the quantity to divide is 5,

I  need to get

C2=2

D2=2

E2=1

F2=0(blank)

[#6 · Accepted solution]

=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))

[#7]

Thank you for the response, I am unable to get the expected results

When I am pasting the formula in C2, it results in circulate reference

[#8]

It is because your sheet keeps my previous version formula.  You may clear C2, D2, E2 and F2.  Then, paste the latest formula to C2.

[#9]

hmm..Thank you  Willy for the support

its solved my issue

Thank you!

[#10]

I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable. 

Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5

The formula should be variable to be copied across 2 cells or 20.

Thoughts?

Allain

allainm@gmail.com

[#11]

I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable. 

Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5

The formula should be variable to be copied across 2 cells or 20.

Thoughts?

Allain

allainm@gmail.com

[#12]

I am so sorry for the late reply.  I am not sure if the following approach suits your needs.

The Formula is

=$A$1/SUMPRODUCT(--(ISFORMULA($B$1:$F$1)))

	A	B	C	D	E	F
1	50	=TheFormula	=TheFormula	=TheFormula	=TheFormula	=TheFormula

[#13]

Thanks for trying, I couldn't get that formula to work at all.

😞

[#14]

May I have your excel version? Does it have IsFormula Function?

[#15]

I have 2016, I don't know what isFormula function is.

[#16]

Reference : IsFormula Function

[#17]

Willy,
It’s a while since you posted this solution - I’ve successfully used your formula, but would there be a way of adjusting it to set a maximum permissible value? I.e. in your example A, B, C etc could be no greater than 8.

Cheers

[#18]

@Willy Lau Hi Willy,

Thanks a lot for addressing this issue.

I have a similar requirement.

I need to distribute quantities in 1 cell among 12 columns (all months of 2020 for a forecast)

I tried applying your formula, but I just got the same number as in the original cell in the cell containing the formula but fails to distribute it among the rest 11 columns. Please help

[#19]

($1:$1=0)

i didnt't understand this thing could you please explain it.@Willy Lau 

[#20]

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.

@Remya Ramakrishnan