Giving a score based on a scale

[npatridge]

I am just having the worst time wrapping my head around this one today. I've tried a bunch of approaches, but the results have been imperfect.

I have a set of metrics that will get a 0-10 score based on their value. That score will determine where they end up on a speedometer chart. I need to be able to define where they cross from red to yellow to green and max out at 10. Here is a table of the values:

0	4	8	10	Value	Score
∞	60	45	0
∞	0.4	0.3	0
∞	0.03	0.02	0
∞	5	2	0
∞	8	5	0
∞	0.01	0.005	0
∞	0.08	0.04	0
∞	0.15	0.1	0
∞	0.15	0.1	0
∞	0.06	0.03	0
∞	0.08	0.04	0

I need a formula I can put in Column F that will give me the correct weighted score 0-10. Anyone know how to do this? Thanks in advance!

[npatridge]

I think I finally got my solution! Here it is if anyone is interested:

=IF(
E2=<C2,
$C$1+(($D$1-$C$1)*(C2-E2)/C2),
IF(
E2=<B2,
$B$1+(($C$1-$B$1)*(B2-E2)/(B2-C2)),
($B$1-$A$1)*(B2/E2)
)
)