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

AmirFirdaus9509 · 2024-05-27T02:32:46-0400

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 · 2024-05-27T02:51:27-0400

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

8

0

6

97

4

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

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 · 2024-05-27T03:00:35-0400

Cubist said:
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 · 2024-05-27T03:18:10-0400

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

AmirFirdaus9509 · 2024-05-27T03:24:43-0400

Cubist said:
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 · 2024-05-27T03:48:43-0400

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