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%)

	A	B	C
1	Option	Cost $	% out of 100%
2	One	45	0%
3	Two	47
4	Three	60
5	Four	65
6	Five	145
7	Six	150	100%

<tbody>
</tbody>

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

 

[Steve_R]

Something like this?

Excel 2010

	A	B	C
1	Option	Cost $	% out of 100%
2	One	45	0.00%
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%

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

Sheet1​

Worksheet Formulas Cell Formula C2 =(B2-MIN(B:B))/(MAX(B:B)-MIN(B:B)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.