Average for multiple 5 Star Rating Columns
Hi,
I have about 8 columns with ratings, all columns have the 5 star rating option, how can I collect an average of all columns?
Thank you! š
Best Answer

You would take this formula:
=IF([1st Column]@row = "Empty", 0, IF([1st Column]@row = "One", 1, IF([1st column]@row = "Two", 2, IF([1st Column]@row = "Three", 3, IF([1st Column]@row = "Four", 4, 5))))
Then you would copy it and change it to [2nd Column]. Then again for the third column and again for the 4th column, so on and so forth until you have the IF written out for each of your 8 columns.
Then you would nest each of those IF statements in the AVG function.
=AVG(IF([1st Column]@row = "Empty", 0,...............), IF([2nd Column]@row = "Empty", 0,...............), IF([3rd Column]@row = "Empty", 0,...............), ...........................)
Answers

You would need to use a helper column or multiple helper columns with a formula to convert the stars into usable numbers. It could be done without helper columns, but the formula gets rather bulky rather quickly.
=IF([1st Column]@row = "Empty", 0, IF([1st Column]@row = "One", 1, IF([1st column]@row = "Two", 2, IF([1st Column]@row = "Three", 3, IF([1st Column]@row = "Four", 4, 5))))
You would then duplicate this for each of the other columns to output a numerical value based on the number of stars.
If you wanted to keep everything in a single column, you would write out each IF for each of the columns, separate them with commas, then nest them in an AVG function.
=AVG(IF([1st Column]@row = 0, 0, .......................................), IF([2nd Column]@row = 0, 0, .......................................), IF([3rd Column]@row = 0, 0, .......................................), .................................)

Hi @Paul Newcome ,
Can you please spell out this formula for me?
"=AVG(IF([1st Column]@row = 0, 0, .......................................), IF([2nd Column]@row = 0, 0, .......................................), IF([3rd Column]@row = 0, 0, .......................................), .................................)"
This is probably what i need, avaerage of all columns.
(Btw. the first formula is simple =[1st Column]@row then it'll give the same data..)
Thank you!

You would take this formula:
=IF([1st Column]@row = "Empty", 0, IF([1st Column]@row = "One", 1, IF([1st column]@row = "Two", 2, IF([1st Column]@row = "Three", 3, IF([1st Column]@row = "Four", 4, 5))))
Then you would copy it and change it to [2nd Column]. Then again for the third column and again for the 4th column, so on and so forth until you have the IF written out for each of your 8 columns.
Then you would nest each of those IF statements in the AVG function.
=AVG(IF([1st Column]@row = "Empty", 0,...............), IF([2nd Column]@row = "Empty", 0,...............), IF([3rd Column]@row = "Empty", 0,...............), ...........................)
Help Article Resources
Categories
Check out the Formula Handbook template!