Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
