# Need to Look at Entire Column for ISBLANK

Options
edited 05/23/24

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.

• ✭✭✭✭✭✭
Options

Try an AVG/COLLECT combo.

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

• ✭✭✭✭✭✭
Options

Try an AVG/COLLECT combo.

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

• Options

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.

• Options

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.

• Options
• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!