Average
I would be grateful if someone could tell me what to do.
I want to find the average of columns A and B on a sheet. If column A has data and column B does not, what formula should I use?
Best Answer

Hi @Kenji
Thank you for clarifying! In this case, you can wrap an IFERROR Function around your formula. This says if there's an error (because there is no data), then return a blank cell:
=IFERROR(AVG([ColumnA]:[ColumnB]), "")
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

=AVG([ColumnA]:[ColumnB])

Hi,
Thank you very much for the answer.
I tried it and got #DIVIDE BY ZERO.
If Columns A and B have data, it will work, but my case is there is no data in Column B.

Hi @Kenji
Would you be able to provide a screen capture of the columns you're referencing? If one column has numbers and the other is completely blank, you should still be able to see a number with the AVG formula provided above:
Do you have any formulas in these referenced columns? If so, is it possible that there's a #DIVIDE BY ZERO error in either ColumnA or ColumnB? If there's an error in one of those cells, it will appear in this other formula that's referencing it.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
I am very sorry, but I misunderstood my sheet.
What I wanted to know was the case where there was no data in both Column A and Column B. In that case, I want to make sure that nothing is displayed in the Average cell.

Hi @Kenji
Thank you for clarifying! In this case, you can wrap an IFERROR Function around your formula. This says if there's an error (because there is no data), then return a blank cell:
=IFERROR(AVG([ColumnA]:[ColumnB]), "")
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!