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

 

[Coyotex3]

Something like this might work:

=IFS(B1>=1.2,4,B1>=1.1,3.5,B1>=0.9,3,B1>=0.84,2,B1>=0.75,2,B1>=0.67,1.5,B1>=0.6,1,B1>=0.55,0.5,B1<=54.99,0)

This assume your data is on Column A. Please place this formula in Column B.

 

[dreid1011]

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)

 

[suertetres]

Something like this might work:

=IFS(B1>=1.2,4,B1>=1.1,3.5,B1>=0.9,3,B1>=0.84,2,B1>=0.75,2,B1>=0.67,1.5,B1>=0.6,1,B1>=0.55,0.5,B1<=54.99,0)

This assume your data is on Column A. Please place this formula in Column B.

Thank you- i tried and it returned a 0 for the value in the B cell

 

[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)

Thakn you for this. Would you mind explaining this in a little more detail? While I'm pretty versed in basic Excel functions, this is a bit above my level of knowledge. What are columns D and E in your example? Would I just put the table off on a different page?

 

[dreid1011]

See column G for the formula only version:

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

Cell Formulas
Range	Formula
E2:E10	E2	=LOOKUP(D2,$A$2:$A$10,$B$2:$B$10)
G2:G10	G2	=LOOKUP(D2,{0,0.55,0.6,0.67,0.75,0.84,0.9,1.1,1.2},{0,0.5,1,1.5,2,2.5,3,3.5,4})

 

[dreid1011]

Thakn you for this. Would you mind explaining this in a little more detail? While I'm pretty versed in basic Excel functions, this is a bit above my level of knowledge. What are columns D and E in your example? Would I just put the table off on a different page?

Yes, you can put the table wherever you want it to be. Another sheet would help keep it out of the way. Columns D and E are my test values to show the formula working and for you to decide if those are the values you expect to get from it.

 

[suertetres]

Yes, you can put the table wherever you want it to be. Another sheet would help keep it out of the way. Columns D and E are my test values to show the formula working and for you to decide if those are the values you expect to get from it.

Thank you so much for helping, I'm going to try and implement this now. Again- truly appreciate you.

 

[Coyotex3]

Thank you- i tried and it returned a 0 for the value in the B cell

Do you have Data in Column A?

 

[dreid1011]

Let us know how it works, and you're welcome. I am happy to help.