Remove DIV/0 in an AVERAGE function

[dfowers]

I am trying to get a blank cell where I am averaging a range of cells instead of the DIV/O error. There are blanks in all of the cells right now, which is causing the error. I have tried using the IF function, but it doesn't seem to work:

Please Login or Register  to view this content.

I have also used

Please Login or Register  to view this content.

Both still return #DIV/0!. Is there a way to just show a blank cell until I start adding data into the spreadsheet?

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[camalone319]

This should work:

=IFERROR(AVERAGE(D2:O2),"")

[dfowers]

TMS, I get a message stating there is an error in the formula, but it doesn't specify what it is.

Never mind -- I just needed to change the 02 to an O2. Thanks so much to you and Camalone319.

[TMS]

I don't get any error message.

What character is highlighted when you click OK?

Maybe you need to change the comma to a semi-colon.

Please Login or Register  to view this content.

[TMS]

Interesting that you have two identical solutions offered.

Maybe you should post a sample workbook.

Regards, TMS

[dfowers]

Sorry about that - I copied your reply, but it appears that you used a zero instead of an O in my formula. Once I corrected that, it worked perfectly!

[TMS]

Mmmm. I copied one of your formulae from your original post and amended it ...

Please Login or Register  to view this content.

Good to use the formula tags ... you can see things more clearly.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[dfowers]

Thanks, TMS. I learned a lot today. This was my first visit and post to the Forum and it's been a long time since I used Excel formulae, so I really appreciate the help