Assigning a Value Based on a Range

[suertetres]

Hi Everyone, I've searched, but may be calling it the wrong thing...I'm trying to assign a point range for a specific set of numbers and need help with the formula. The number after the = sign is the point i want to assign. Is there an if/then statement that could accomodate this large of a range?

The ranges are as follows:
>= 120% = 4
110.00%-119.99% = 3.5
90.00%-109.99%= 3
84%-89.99%= 2.5
75%-83.99%=2
67%-74.99%= 1.5
60%-66.99%= 1
55%-59.99%= .5
<54.99% = 0

 

[suertetres]

You could make a lookup table like the following, and use the formula to reference the table. You can also do this just in a formula without the lookup table, but then you would have to update the formula anytime a range/score changes instead of just changing it in the table.

Book1
	A	B	C	D	E
1	Range	Points
2	0%	0		95.00%	3
3	55%	0.5		84.00%	2.5
4	60%	1		113.00%	3.5
5	67%	1.5		72.00%	1.5
6	75%	2		59.99%	0.5
7	84%	2.5		135.00%	4
8	90%	3		74.99%	1.5
9	110%	3.5		54.99%	0
10	120%	4		119.99%	3.5
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=LOOKUP(D2,$A$2:$A$10,$B$2:$B$10)

I think this is 100% user error on my part. I may not have fully asked my question correctly.

If i have a cell with 85%, based on the range I listed, it would assign a point value, with the ranges I have listed, 85% would be equal to 2.5 points. I would want the cell next to the 85% to reflect 2.5. I hope tihs makes sense. Again, appreciate your help!

 

[dreid1011]

I think this is 100% user error on my part. I may not have fully asked my question correctly.

If i have a cell with 85%, based on the range I listed, it would assign a point value, with the ranges I have listed, 85% would be equal to 2.5 points. I would want the cell next to the 85% to reflect 2.5. I hope tihs makes sense. Again, appreciate your help!

Right, so with the lookup table, you need to use the formula I had in column E to pull the point value from the table. If you use the formula only option in post #6, you can just use the formula in the cell next to your %'s.

 

[suertetres]

Right, so with the lookup table, you need to use the formula I had in column E to pull the point value from the table. If you use the formula only option in post #6, you can just use the formula in the cell next to your %'s.

Thank you, it worked!!!! I have been searching high and low and have tried so many things. It's been years since I used the old school forums for an answer and low and behold, bam- it worked. thank you again!

 

[dreid1011]

You're welcome, however, I would ask that you mark the post with the formulas as the solution if you could please. That way others can find it quicker in the future.

 

[suertetres]

Done- sorry in my excitement I checked the wrong post.