# Website Like Ratings

I was looking for hosting solutions for beatexcel when I saw ratings for hosting companies on almost all affiliate sites. I wondered whether we can use website like ratings in our reports. So I decided to give it a try and make it in Excel.

Here is an example of how website like ratings look like: How can we make this kind of ratings in Excel? Here are the steps: Now it is time to setup our website like rating interface. We are going to get a sum of votes for each company for selected hosting plan type with an easy Coutifs formula. We are going to use cells E6:I6 for stars and J6 for rating value. E6:I6 range will get its values based on rating value (J6). Therefore it is better to start calculating rating value first. We will do it with another easy formula. This time we will use Averageifs. Now it is time to arrange our rating stars. We want our cells to display a full, half or empty star based on rating value. For example, we want 2.5 stars for a rating of 5 and 3 stars for a rating of 6. To achieve this effect, we need to setup formulas into cells E6:I6 to get required values.

E6: =IF(AND(\$J6>0,\$J6<1),0.5;IF(\$J6>1,1,0))

F6: =IF(AND(\$J6>2,\$J6<3),0.5,IF(\$J6>3,1,0))

G6: =IF(AND(\$J6>4,\$J6<5),0.5,IF(\$J6>5,1,0))

H6: =IF(AND(\$J6>6,\$J6<7),0.5,IF(\$J6>7,1,0))

I6: =IF(AND(\$J6>8,\$J6<9),0.5,IF(\$J6>9,1,0))

Here is what happens when you apply these formulas into these cells. Now select cells E6:I6 and apply conditional formatting. Choose stars from icons sets. Open manage rules window and uncheck Show Icon Only checkbox. It is supposed to look like this at this stage: Now copy formulas into other rows and make some formatting. That will complete your website like ratings. Here is how mine looks: ### 2 Responses

1. Raj says:

• melih says: