Average for multiple 5 Star Rating Columns

A Rose

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! 😃

Paul Newcome

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,...............), ...........................)

Paul Newcome

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, .......................................), .................................)

A Rose

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!

Paul Newcome

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,...............), ...........................)