negative numbers to percent

[Scorpion]

i have a column of numbers that range from -5000 up to 150,000 and would like
to place the numbers into perspective from 0% to 100% but i can't figure it
out , can someone help.

 

[Fred Smith]

What you are talking about is an index. You have a range of 155,000 and you
want to break it into 100 parts. So each part is 1550. If:

a1 = -5000 (index value 0)
a2 = 150000 (index value 100)

To determine index value of b1, you want:
=(b1-$a$1)/($a$2-$a$1)

Format as percent.

Regards
Fred.

 

[Max]

Another thought: =PERCENTRANK({-500;150000},B1)
where B1 houses the value

 

[Scorpion]

Hey Fred , that works perfectly.
i can't thank you enough, i'm sure glad you responded.
Dan