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%)
<tbody>
</tbody>
Thank you very much in advance for your time and help!
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!