I set up a spreadsheet to allow a group of people to rate a list of items. Each item is a row in my spreadsheet, and each person has a column, where they can choose 1 to 5 stars. I need to calculate the average rating for each row. Some people did not provide a rating for each item in the list, which means that the denominator in my average formula needs to be different for each row. Is there a way to set this up as a column formula, or do I need to set up a separate formula for each list item?
You can work with a column formula like
=IFERROR(VALUE(SUM(C,D,E,F))/COUNTA(C,D,E,F),"")
The key point to note is the functions need to have columns specified explicitly.
Column formulas can only contain references to columns or to fixed cells , not relative cells or ranges.
. See https://support.spreadsheet.com/hc/en-us/articles/360039462851-Column-formula
