Equation: equals number if another number is between a certain range

[njsutorius]

I would like to have a grade scale so that where c3 = wherever b3 falls in the range in column A.

Thus if B3 is 10 then C3 would equal 4.5 on the scale range.

We can breakout the range in A if needed into multiple columns if necessary.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1-5 = 0
[/TD]
[TD]CPH
[/TD]
[TD]Score
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]6-8 = 3.5
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]9-11 = 4.5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]12+ = 5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for any assistance.

 

[shg]

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

CPH​

[/td][td]

Score​

[/td][td]

CPH​

[/td][td]

Score​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

0​

[/td][td]

0.0​

[/td][td]

10​

[/td][td]

4.5​

[/td][td]D2: =LOOKUP(C2, $A$2:$B$5)[/td][/tr]

[tr][td]

3​

[/td][td]

6​

[/td][td]

3.5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

9​

[/td][td]

4.5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

12​

[/td][td]

5.0​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[njsutorius]

You win the prize of my thankfulness. You are a great resource to helping your fellow man. Thanks!

 

[shg]

Wow, you're welcome.

 

[mfexcel]

vlookup would also be a common solution for your problem.

Hope this helps you understand better
vlookup – True or False?? | wmfexcel

 

[njsutorius]

Ya that vlookup is pretty much right up my ally on this question. I am going to read your article in detail as I think vlookup may be useful for some of my other ideas as well.