How to get a total add up number from total number in another row??

[AmirFirdaus9509]

Hi , apologize if this question sound more difficult than it should be

I would like to create a formula that is able to properly capture a certain amount of number and distribute as evenly as possible from total number in cell C16.
The total delivery is 725.

Im trying to figure out a formula in cell D2 until D15 where out of sum of 725 in cell C , the total number in cell D2 until D15 is add up to 30.

Any help is greatly appreciate

 

[Cubist]

One way is to divide proportionally to column C.

Book1
	C	D	E
1	Total Delivery	Total Delivery Pickup Daily	Check
2	108	4
3	63	3
4	114	5
5	8	0
6	97	4
7	7	0
8	37	2
9	53	2
10	29	1
11	25	1
12	23	1
13	15	1
14	68	3
15	78	3
16	725	30	30
Sheet5

Cell Formulas
Range	Formula
D2:D15	D2	=ROUND(C2/$C$16*$D$16,0)
C16	C16	=SUM(C1:C15)
E16	E16	=SUM(D2:D15)

 

[AmirFirdaus9509]

One way is to divide proportionally to column C.

Book1
	C	D	E
1	Total Delivery	Total Delivery Pickup Daily	Check
2	108	4
3	63	3
4	114	5
5	8	0
6	97	4
7	7	0
8	37	2
9	53	2
10	29	1
11	25	1
12	23	1
13	15	1
14	68	3
15	78	3
16	725	30	30
Sheet5

Cell Formulas
Range	Formula
D2:D15	D2	=ROUND(C2/$C$16*$D$16,0)
C16	C16	=SUM(C1:C15)
E16	E16	=SUM(D2:D15)

Hi , thanks for the reply

Is there a way for cell C5 and C7 to be pick up also out of 30?
I would like that at least 1 Name is pick for total add up

 

[Cubist]

Who do you want to take away from to give to the 0's?

 

[AmirFirdaus9509]

Who do you want to take away from to give to the 0's?

Probably from the highest Total Delivery as they have more chance to be pick up during the division.

Is it possible for the formula given to capture each row C at minimum value of 1?

 

[Cubist]

This will guarantee at least 1 per row, however, I assume you want the answer in whole numbers. There isn't a one-size-fits-all rounding rule that will guarantee the sum adds up to 30. There will be a little manual adjustment. Sometimes you'll be short 1, sometimes you'll be over 1.

Book1
	D	E	F	G
1	Total Delivery	Total Delivery Pickup Daily	Check	Round to whole number
2	108	3.38		3.00
3	63	2.39		2.00
4	114	3.52		4.00
5	8	1.18		1.00
6	97	3.14		3.00
7	7	1.15		1.00
8	37	1.82		2.00
9	53	2.17		2.00
10	29	1.64		2.00
11	25	1.55		2.00
12	23	1.51		2.00
13	15	1.33		1.00
14	68	2.50		3.00
15	78	2.72		3.00
16	725	30	30	31.00
Sheet5

Cell Formulas
Range	Formula
E2:E15	E2	=1+D2/$D$16*($E$16-ROWS($D$2:$D$15))
G2:G15	G2	=ROUND(E2,0)
D16	D16	=SUM(D1:D15)
F16	F16	=SUM(E2:E15)
G16	G16	=SUM(G2:G15)

 

[AmirFirdaus9509]

This will guarantee at least 1 per row, however, I assume you want the answer in whole numbers. There isn't a one-size-fits-all rounding rule that will guarantee the sum adds up to 30. There will be a little manual adjustment. Sometimes you'll be short 1, sometimes you'll be over 1.

Book1
	D	E	F	G
1	Total Delivery	Total Delivery Pickup Daily	Check	Round to whole number
2	108	3.38		3.00
3	63	2.39		2.00
4	114	3.52		4.00
5	8	1.18		1.00
6	97	3.14		3.00
7	7	1.15		1.00
8	37	1.82		2.00
9	53	2.17		2.00
10	29	1.64		2.00
11	25	1.55		2.00
12	23	1.51		2.00
13	15	1.33		1.00
14	68	2.50		3.00
15	78	2.72		3.00
16	725	30	30	31.00
Sheet5

Cell Formulas
Range	Formula
E2:E15	E2	=1+D2/$D$16*($E$16-ROWS($D$2:$D$15))
G2:G15	G2	=ROUND(E2,0)
D16	D16	=SUM(D1:D15)
F16	F16	=SUM(E2:E15)
G16	G16	=SUM(G2:G15)

Apologize for my belated reply ,
I have take your suggestion into the current build and able to get the result !!

Thank you very much!