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.
Best Answer
-
Try an AVG/COLLECT combo.
=AVG(COLLECT(Rating:Rating, Rating:Rating, @cell <> "", [Area of Review]:[Area of Review], @cell = "E-commerce"))
Answers
-
Try an AVG/COLLECT combo.
=AVG(COLLECT(Rating:Rating, Rating:Rating, @cell <> "", [Area of Review]:[Area of Review], @cell = "E-commerce"))
-
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.
-
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
Check out the Formula Handbook template!