Star Rating

When we want to give our opinion about a product, we are often able to evaluate it by assigning a certain number of stars. Our rating updates the previous state, and the product score is always a number. How do you calculate how many total stars a product should have based on the score number? There are several ways to do this in Excel, and one of them is by applying the Conditional Formatting option.

A list of the 10 best films, according to the visitors, was taken from the site imdb.com. In the table, we first list the title of the film, and then the score. The following are 10 columns numbered 1-10. To calculate in which column the star should be displayed, we will write the formula:

=RANK.EQ($B2;$B$2:$B$11;1)-C$1+1

The RANK.EQ function is used to calculate the rank of the current movie score in relation to the array of scores, and since the last parameter of the function is number 1, this means that the rank will be displayed in descending order. It is necessary to subtract the number of stars from the obtained value, and then add the result with 1, which gives us a value needed to estimate whether the given position in the table should contain a star and what kind.

Then you need to select all the values ​​in the table where the stars should appear, enter the Conditional Formatting menu and select the option for formatting using the star icons.

Select the Show Icon Only check box, and then set the criterion so that the full star is displayed when the number is greater than 0, and the half-star when the number is between -1 and 0. The formula and conditional formatting criteria determine how the stars will be displayed- this is just one of the ways how to calculate it!

After applying the Conditional Formatting we will get a table with the final number of stars – movie ratings.