Forumla help - Weighting a range of numbers across 100% (proportionally)

[orange12]

Hi there. i am looking for a formula to allocate a percentage out of 100%, to a range of values.

I am evaluating 6 options (Column A) and they have different costs (Column B). I wish to write a formula in Column C that displays the cost as a %, where 0% is the least cost and 100% is the most cost. I do not wish to distribute the 100% evenly over the options, by just adding 16% to each new row. I need to distribute proportionally based on where they sit in the range.

So for example, Option Two is only $47 so should be close to Option One (0%).
Option Five is $145, so should be almost 100%, (as is close to Option Six (100%)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Option[/TD]
[TD]Cost $[/TD]
[TD]% out of 100%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]One[/TD]
[TD]45[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Two[/TD]
[TD]47[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Three[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Four[/TD]
[TD]65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Five[/TD]
[TD]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Six[/TD]
[TD]150[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much in advance for your time and help!

 

[Steve_R]

Something like this?

Excel 2010

	A	B	C
Option	Cost $	% out of 100%
One
Two
Three
Four
Five
Six

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]0.00%[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]1.90%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]14.29%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]19.05%[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]145[/TD]
[TD="align: right"]95.24%[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"]100.00%[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=(B2-MIN(B:B))/(MAX(B:B)-MIN(B:B))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Would something like this work for you?
The Cost$ do not necessary need to be in order.

Excel 2010
	A	B	C
1	Option	Cost $	% out of 100%
2	One	45	0%
3	Two	47	1.90%
4	Three	60	14.29%
5	Four	65	19.05%
6	Five	145	95.24%
7	Six	150	100.00%
Sheet1

Cell Formulas
Range	Formula
C2	=IF(B2=MIN(B$2:B$7),0%,IF(B2=MAX(B$2:B$7),100%,(B2-MIN(B$2:B$7))/(MAX(B$2:B$7)-MIN(B$2:B$7))))

C2 formula copied down.
C column formatted as Percentage.

 

[jtakw]

Steve has a better formula, I guess I didn't need the IF part of my formula.

 

[orange12]

Thanks Steve & Jtakw! elegant solutions. appreciate your help! thank you so much

 

[jtakw]

You're welcome, welcome to the forum.

 

[Steve_R]

You're welcome. Happy the formula helped.