I want a formular that gives a star rating when something is between 2 numbers

[nijode999]

So,

I can figure something out, but I don't think it is a hard one.


So I have a B11 with a number between 1-100.


I want a create a formula that convert this number to stars like this:

★★★★★ 96-100

★★★★½ 86-95

★★★★ 76-85

★★★½ 66-75

★★★ 56-65

★★½ 46-55

★★ 36-45

★½ 26-35

★ 16-25

½ 0-15

What is the best way I can do this. So if B11 is 43 I want B13 to show ★★ and when B11 is 76 I want B13 to show ★★★★ stars. Any way to do this?

Already thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	B
11	67
12
13	★★★☆
Sheet1

Cell Formulas
Range	Formula
B13	B13	=IFNA(REPT("★",LOOKUP(B11,{16,36,56,76,96},{1,2,3,4,5})),"")&IF(OR(B11<16,AND(B11>25,B11<36),AND(B11>45,B11<56),AND(B11>65,B11<76),AND(B11>85,B11<96)),"☆","")

 

[Fluff]

A slightly simpler formula

=IFNA(REPT("★",LOOKUP(B11,{16,36,56,76,96},{1,2,3,4,5})),"")&IF(OR(B11<16,ISODD(INT(B11-16)/10)),"☆","")

 

[nijode999]

Good try! But that doesn't have half stars in it and only 4 in stead of 5 stars.

 

[Fluff]

There is no half star, so I used the outline instead.
Nor sue what you mean about 4stars instead of 5

 

[nijode999]

That the formula only show 4 stars all full score. But it should show 5 stars between 96-100% like the rating scale in the opening post :D

 

[nijode999]

 

[Fluff]

it should show 5 stars between 96-100% like the rating scale in the opening post

That's exactly what it does.

+Fluff v2.xlsm
	B	C
11	99
12
13	★★★★★	★★★★★
Sheet1

Cell Formulas
Range	Formula
B13	B13	=IFNA(REPT("★",LOOKUP(B11,{16,36,56,76,96},{1,2,3,4,5})),"")&IF(OR(B11<16,ISODD(INT(B11-16)/10)),"☆","")
C13	C13	=IFNA(REPT("★",LOOKUP(B11,{16,36,56,76,96},{1,2,3,4,5})),"")&IF(OR(B11<16,AND(B11>25,B11<36),AND(B11>45,B11<56),AND(B11>65,B11<76),AND(B11>85,B11<96)),"☆","")

 

[nijode999]

Ahh yeah now see it! Is it possible to implement the ½ for the half star ratings, because half star icon doesnt exist. Would love that :D

 

[Fluff]

How about

=IFNA(REPT("★",LOOKUP(B11,{16,36,56,76,96},{1,2,3,4,5})),"")&IF(OR(B11<16,ISODD(INT(B11-16)/10)),"½","")