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:

website-like ratings

How can we make this kind of ratings in Excel? Here are the steps:

We have our votes listed in a sheet named “Votes”.

website-like ratings 1

Now it is time to setup our website like rating interface.

website-like ratings 2

We are going to get a sum of votes for each company for selected hosting plan type with an easy Coutifs formula.

website-like ratings 3

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.

website-like ratings 4

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.

 

website-like ratings 5

Now select cells E6:I6 and apply conditional formatting. Choose stars from icons sets. Open manage rules window and uncheck Show Icon Only checkbox.

website-like ratings 6

It is supposed to look like this at this stage:

website-like ratings 7

Now copy formulas into other rows and make some formatting. That will complete your website like ratings. Here is how mine looks:

website-like ratings

You can download and inspect it yourself from this link: Download Website Like Ratings

You may also like...

2 Responses

  1. Raj says:

    Dear Melih, can’t download the file. Can you please look into this.

Leave a Reply

Your email address will not be published. Required fields are marked *