Sliding scale and to return a percent based an a range in reverse order

Copper Contributor

I want to return a percent based on a scale that is in reverse order.  ie lower number is the better number.

 

Example

50%   $12.50

75%   $11.00

100%  $9.75

 

Lookup value  $10.55  is "What Percentage?" based on the chart above?

2 Replies

@CKoptical 

Do you want linear interpolation? If so:

Let's say the lookup value is in D1.

The percentage is

=IF(D1<9.75,"?",IF(D1<11,100%-(D1-9.75)/(11-9.75)*(100%-75%),IF(D1<=12.5,(D1-11)/(12.5-11)*(75%-50%),"?")))

@Hans Vogelaar 

 

Thank you,  It worked perfectly!