return stars based on where it falls in graph?

[alesha711]

I want column 4 to return the stars based on where the data falls in the table. I consider myself pretty good with excel but this one has me stumped. I though and if function couldn't figure that one out. Here is the Data

Thanks!

Was going to attach table but it says I cant... here is images.

Facility	RNs	Total Nursing	Stars
1	.531	4.028
2	.335	3.606
3	.758	3.703
4	1.255	6.075
5	.761	3.504

<tbody>
</tbody>

Here is how the stars lies (in my spreadsheet I have pics of stars using wingdings symbol)
....................................................................................Total Nursing

RN	<3.176 <tbody> </tbody>	3.176-3.551 <tbody> </tbody>	3.552-4.009 <tbody> </tbody>	4.010-4.237 <tbody> </tbody>	>=4.238 <tbody> </tbody>
<0.246 <tbody> </tbody>	*	*	**	**	***
0.246-0.382 <tbody> </tbody>	*	**	***	***	***
0.383-0.586 <tbody> </tbody>	**	***	***	****	****
0.587-0.883 <tbody> </tbody>	**	***	****	****	****
>=0.884 <tbody> </tbody>	***	***	****	****	*****

<tbody>
</tbody>

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Facility	RNs	Total Nursing	Stars					Total Nursing
2	1	0.531	4.028	****				RN	0	3.176	3.552	4.01	4.238
3	2	0.335	3.606	***				0	*	*	**	**	***
4	3	0.758	3.703	****				0.246	*	**	***	***	***
5	4	1.255	6.075	*****				0.383	**	***	***	****	****
6	5	0.761	3.504	***				0.587	**	***	****	****	****
7								0.884	***	***	****	****	*****

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula D2 =INDEX($I$3:$M$7,MATCH(B2,$H$3:$H$7),MATCH(C2,$I$2:$M$2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Notice how I rearranged your table a little.

 

[alesha711]

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Facility	RNs	Total Nursing	Stars					Total Nursing
2	1	0.531	4.028	****				RN	0	3.176	3.552	4.01	4.238
3	2	0.335	3.606	***				0	*	*	**	**	***
4	3	0.758	3.703	****				0.246	*	**	***	***	***
5	4	1.255	6.075	*****				0.383	**	***	***	****	****
6	5	0.761	3.504	***				0.587	**	***	****	****	****
7								0.884	***	***	****	****	*****

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula D2 =INDEX($I$3:$M$7,MATCH(B2,$H$3:$H$7),MATCH(C2,$I$2:$M$2)) <tbody> </tbody>

<tbody>
</tbody>

Notice how I rearranged your table a little.

That worked perfect THANK YOU! I feel like such a dummy now though, I knew to use index match but was trying to figure out how to put the ranges. Never occured that it would only be 1 value because it stops at the next, sheesh. Brain malfunction! Thanks again!

 

[Eric W]

Happy to help!