Need to Look at Entire Column for ISBLANK

This formula works for just the one row and returns either blank or the number inputted into the row where the formula resides:

=IF(ISBLANK(Rating@row), "", AVERAGEIF([Area of Review]:[Area of Review], "E-commerce", Rating:Rating))

But I need to look at the entire column called Rating to ignore those left blank and then average the entire row called Rating with the number inputted for the remaining items in the Rating column for my Area of Review.

Any suggestion would be greatly appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

Try an AVG/COLLECT combo.

=AVG(COLLECT(Rating:Rating, Rating:Rating, @cell <> "", [Area of Review]:[Area of Review], @cell = "E-commerce"))

All comments

Paul Newcome

Try an AVG/COLLECT combo.

=AVG(COLLECT(Rating:Rating, Rating:Rating, @cell <> "", [Area of Review]:[Area of Review], @cell = "E-commerce"))

tesman284

Paul, thank you for your feedback but your example this is not averaging the entire column.

Example:

RATING

Row1: 9

Row2:

Row3: 3

In my quick example above, I want to look at column RATING and number inputted the ROW1:ROW3 but since Row2 is blank I do not want to include it in my average. In my example the average would be (for this example) 6 since I do not want to include Row2.

My original formula that I am using and works to average the Rating is =IFERROR(AVG(COLLECT(Rating:Rating, [Area of Review]:[Area of Review], "E-Commerce")), 0) but this formula is not ignoring the " " rows and since they are being included, they are skewing my overall average.

Hope this added information helps.

tesman284

Paul, my mistake. Your process did work; I did not have enough information in my test sheet to confirm, but added more data and was able to confirm. Thank you again for your feedback.

tesman284

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.