#div/0 in average formula

[dbagnell]

Hello fellow Excel users!

I have read many answers to the question I have, but I still can't figure it out. I don't think I'm quite good enough to read an answer and apply it to my own formula.

I have an average formula that reads: =AVERAGE(T6,T31,T56,T81,T106). I want it NOT to display a #div/0 if there is no average to return. Answers I've found seem to apply to average listed as T6:T8 for example, but not the way I've listed it. Can you help? I'd be so grateful.

Thanks!

 

[FormR]

Welcome to the Forum!

You could try:

=IFERROR(AVERAGE(T6,T31,T56,T81,T106),0)

 

[dbagnell]

Welcome to the Forum!

You could try:

=IFERROR(AVERAGE(T6,T31,T56,T81,T106),0)

That did it! Thanks a bunch!

 

[mfexcel]

why you wanna display "0" when there is at least one error in your source?

my point is...
1, 2, 3, -2, -4 gives you an average of 0
1, 2, 3, -2, #div/0 also give you an average of 0 (if you wrap your average with IFERROR...)

are you sure you want that?

 

[FormR]

why you wanna display "0" when there is at least one error in your source?

@mfexcel - I read it as the #div/0 error occurs when either all the cells are blanks or zeros.

I want it NOT to display a #div/0 if there is no average to return.

 

[mfexcel]

Hi FormR, you are right the #div/0 may appear will all the cells are blanks... but not when all are zeros. I was not questioning the solution you provided as that seems to be what the OP requests. I just tried to raise a point for consideration.

@mfexcel - I read it as the #div/0 error occurs when either all the cells are blanks or zeros.