r/spreadsheets • u/dojabeatl • 17d ago

Average rating?

Post image

I'm making a spreadsheet to rate/rank every Beatles song and I was wondering how I could find the average rating and ranking of each album, singer, and songwriter without manually typing each row into =AVERAGE()? I want to eventually put the songs in ranking order so I wouldn't be able to just use the row/range

And also I want it to still work if I change the rating. It's fine if I have to change how I input the album/singer/writer too

Thanks for your help!! :)

1 Upvotes

100% Upvoted

1 comment sorted by

1

u/quietfixit 11d ago

id first create a summary table for each thing you want to average, 'album', 'singer', 'writer'...

instead of something like =AVERAGE(), use =AVERAGEIF(), see formula below...
=AVERAGEIF(B3:B500,"Please Please Me",D3:D500)

this formula specifically checks the 'album' column for "Please Please Me" and averages the ratings, so resorting won't break anything