Converting Average Scores/Ratings Into Star Ratings

After 6 books this first month of the year, I'm quite surprised that the short novella and series starter A Spindle Splintered has turned out to be the best I rated thus far. But this whole endeavour is done as scientifically as possible on such a subjective topic. With the CAWPILE rating system... 8.91 is the highest average rating thus far. It's a 4 1/2 star read, but we all know Goodreads doesn't allow half star ratings. 

My spreadsheet includes 10 columns to help me with this. The 7 columns for the 7 categories are added up and divided by 7 in the 8th column. The 9th column assigns a half star rating to each range of the ratings. The 10th column adjusts those rating ranges so that a low half star rating is rounded down and a high half star rating is rounded up.

If anyone is interested in these formulas and ranges, please see below. Please note: I do not believe that these work in Excel. I know they work phenomenally in Google Sheets. I based the second formula off the first formula that was developed for Sheets by G @Book Roast and adjusted by Renee @Addicted To Romance and then further adjusted by myself.

Converting Averaged Score to Half Star Ratings:

=IF(BH3<=0," ", IF(BH3<1.65,"1",IF(BH3<2.299,"1.5", IF(BH3<3.4, "2", IF(BH3<4.599, "2.5", IF(BH3<5.75, "3", IF(BH3<7, "3.5",IF(BH3<7.95, "4", IF(BH3<9, "4.5", IF(BH3<9, "5", "5"))))))))))

Converting Averaged Score to Rounded Star Ratings:

=IF(BH3<=0," ", IF(BH3<1.97,"1", IF(BH3<4, "2", IF(BH3<6.4, "3", IF(BH3<8.5, "4", IF(BH3<9, "5", "5"))))))

BH is the 8th column of the ratings portion. Yes, I have that many columns. A lot of columns are also hidden because they are not necessary for data, but necessary for conditional formatting. It's best to type it into the top cell that contains an average. Imagine for a moment that Row 1 is a sheet title, Row 2 is the column headers, making Row 3 the first one that lists a book.

If you have fewer columns, you won't be using BH. Replace this with whatever column letter your average column is listed in.

If you're not interested in the columns of my Log page, move along. S'all good. No offense taken.

Log Page Columns

A - Month Read

B - Start Date (MM/DD/YY format)

C - Finish Date

D - X of Days (Formula: =C3-B3+1)

E - (HIDDEN) Copy of Avg Rating (Formula: =BH:BH)

F - Book Title

G - Read Status

H - Series Book #

I - Series Title

J - Author

K - Nationality

L - Author Status to me (New to Me, Read Before, Debut in 2023 Author)

M - Original Publication Year

N - Translated

O - Original Language

P - Publisher/Imprint

Q - Publisher Type (Independent, Big 5/4, Self-Published)

R - Length (such as Novel, Novella, Short Stories, Omnibus, Reference book)

S - Pages

T - Book Length (ranges of 50 pgs ea, starts with Under 100 & ends with Over 1000)

U - Hours (Audio book, 00:00:00 format)

V - Narrator

W - Readership (Young Adult, New Adult, Adult, etc.)

X - Format Read. I included a category of "Mixed Media" when I read in multiple formats.

Y - Acquired. Library/Libby, Audible, Kindle, Amazon, etc.

Z - TBR Read or Buy. This might get deleted. Options: Purchased; TBR Read; Want to Buy.

AA - Series or Standalone

AB - Series Finished Checkbox

AC - Genre

AD - Subgenre

AE - Notable Subgenres (Thinking about making this a multiple selection once I learn how.)

AF - Diversity Checkbox

AG - Diversity Type

AH - Diverse Sub-Category

AI - Setting (such as the primary country or if it's some kind of fantasy world)

AJ - Setting State (Drop Down includes US States and Canadian Provinces)

AK - Setting Time Era (Includes Fantasy eras: Dystopian, Futurist, Sword and Sorcery)

AL, AM, AN - Tropes & Keywords

AO - (HIDDEN) Country (not sure why this is here, just that I hid it)

AP, AQ - Character Types

AR, AS - Themes (Topics, Themes, etc.)

AT - Trigger Warnings and Notes

AU - Royal Pinkness Review Checkbox

AV - Goodreads Review Checkbox

AW - DNF? Checkbox

AX - Main DNF Reason

AY - Challenge Book Checkbox

AZ - Book Club Checkbox

BA, BB, BC, BD, BE, BF, BG - CAWPILE Category Ratings

BH - Average

BI, BJ, BK, BL, BM, BN - (HIDDEN) Star T/F Columns to colorize the Star Rating columns.

BO - Half Star Rating

BP - Rounded Rating

BQ - (HIDDEN) Monthly Ranking (Something I'm playing around with to try ranking the books.)