Formula for Star Rating that Ignores Blank Cells and O Values

Amy Arnold

I would like to Average the Star Ratings provided by seven people in the Rate Column.

The formula I have in the Rate Column is NOT ignoring blank cells or cells with a 0 value.

The image below shows the hidden columns that are connected to the formula.

=AVG([DH#]@row, [JH#]@row, [RH#]@row, [JM#]@row, [RF#]@row, [KM#]@row, [CY#]@row)

I am not super savvy with formulas. I appreciate any suggestions.

Find more posts tagged with

Formulas

Accepted answers

SoS | Dan Palenchar

Personally, I would reorganize your columns to move all of the hidden columns to the right, so your numerical outputs would be one block from CY# to RH#.

Then, you can use

=AVERAGEIF([CY#]@row:[RH#]@row, >0)

With your current Sheet structure, something like the below might work but I haven't tested it.

=AVG(COLLECT(Cunesha@row:[RH#]@row, Cunesha@row:[RH#]@row, AND(ISNUMBER(@cell), @ cell>0))

Amy Arnold

@Dan Palenchar The first formula worked perfectly! I moved the columns CY# to RH# into a block and plugged in your formula. Thank you 🙌

💯 =AVERAGEIF([CY#]@row:[RH#]@row,

All comments

SoS | Dan Palenchar

Personally, I would reorganize your columns to move all of the hidden columns to the right, so your numerical outputs would be one block from CY# to RH#.

Then, you can use

=AVERAGEIF([CY#]@row:[RH#]@row, >0)

With your current Sheet structure, something like the below might work but I haven't tested it.

=AVG(COLLECT(Cunesha@row:[RH#]@row, Cunesha@row:[RH#]@row, AND(ISNUMBER(@cell), @ cell>0))

Amy Arnold

@Dan Palenchar The first formula worked perfectly! I moved the columns CY# to RH# into a block and plugged in your formula. Thank you 🙌

💯 =AVERAGEIF([CY#]@row:[RH#]@row,

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.