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], "Ecommerce", 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.
Best Answer

Try an AVG/COLLECT combo.
=AVG(COLLECT(Rating:Rating, Rating:Rating, @cell <> "", [Area of Review]:[Area of Review], @cell = "Ecommerce"))
Answers

Try an AVG/COLLECT combo.
=AVG(COLLECT(Rating:Rating, Rating:Rating, @cell <> "", [Area of Review]:[Area of Review], @cell = "Ecommerce"))

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], "ECommerce")), 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.

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.

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!