Distribute values in excel based on weight

[carrot]

I have a total number, 300,000. I want to distribute this number over a list of items, where each item has a weight between 1 and 3. The sum of all numbers should add up back to 300,000. I tried looking for things like weighted distributions, but I'm not sure if that's what this is called.

 

[StephenCrump]

Welcome to the Forum!

Do you mean like this?

C4: =ROUND(B$1*B4/B$10,0) Copy down the column

Excel 2010

	A	B	C
1	Distribute	300,000
2
3		Weight	Amount
4	Item A	1	23,077
5	Item B	3	69,231
6	Item C	3	69,231
7	Item D	2	46,154
8	Item E	3	69,231
9	Item F	1	23,077
10		13	300,001

<tbody>
</tbody>

​

I have assumed that you want integer results, in which case you'll sometimes get minor rounding errors requiring adjustment, as in the example shown.

 

[TaylorNeedsHelp]

Proportionally distribute the weights of questions on an exam when the test-taker answers "N/A" for a question

Hello, I have a question about my own distribution problem. I can't make my own post so I'm writing it here. I don't know who will see this :/

I am weighting the questions of a test differently. Questions can be answered with N/A. I want to proportionally distribute a question's weight that was answered "N/A" to the rest of the weighted questions on the exam. How do I calculated the adjusted weight for each question?

 

[StephenCrump]

Re: Proportionally distribute the weights of questions on an exam when the test-taker answers "N/A" for a question

Welcome to the Forum.

Something like this perhaps?

D2: =IF(C2="N/A",0,B2*B$13/(B$13-SUMIF(C$2:C$11,"N/A",B$2:B$11)))

Book1
	A	B	C	D
1	Question	Weight	Response	Revised Weight
2	1	1		1.25
3	2	2		2.5
4	3	3		3.75
5	4	1	N/A	0
6	5	2		2.5
7	6	4		5
8	7	1		1.25
9	8	2	N/A	0
10	9	1	N/A	0
11	10	3		3.75
12
13	Total	20		20
Sheet1

 

[TaylorNeedsHelp]

Re: Proportionally distribute the weights of questions on an exam when the test-taker answers "N/A" for a question

Welcome to the Forum.

Something like this perhaps?

D2: =IF(C2="N/A",0,B2*B$13/(B$13-SUMIF(C$2:C$11,"N/A",B$2:B$11)))

	A	B	C	D
1	Question	Weight	Response	Revised Weight
2	1	1		1.25
3	2	2		2.5
4	3	3		3.75
5	4	1	N/A	0
6	5	2		2.5
7	6	4		5
8	7	1		1.25
9	8	2	N/A	0
10	9	1	N/A	0
11	10	3		3.75
12
13	Total	20		20

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Yes, thank you!!!!