Online reviews are usually given as averages. But imagine restaurant A has ten 4 star reviews and restaurant B has eight 5 star reviews and two 1 star reviews. Similar averages, but restaurant A basically guarantees a 4 star meal, whereas restaurant B has a 1 in 5 chance of delivering a terrible meal.

I typed this into Co-Pilot:

Given an average rating out of 5 and a count of the number of ratings, and assuming every good review is 5 and every bad review is 1, create a function in excel to calculate the number of bad reviews

Co-pilot’s response:

Let’s assume:
A1 contains the average rating.
B1 contains the total number of ratings.
The formula to calculate the number of bad reviews (where each bad review is rated as 1) is:
= (5*B1 – A1*B1) / 4

Here’s the breakdown of the formula:
5*B1 calculates the total points if all reviews were good (rated 5).
A1*B1 calculates the total points actually received.
The difference (5*B1 – A1*B1) gives the total points lost due to bad reviews.
Dividing by 4 gives the number of bad reviews because each bad review contributes 4 points less than a good review (5-1=4).

Pretty clever!