#DIV/0! ERROR MESSAGE--DON'T WANT IT 1

[MrsTFB]

Hey guys,
I know there is an option somewhere or some sort of choice that will not display the #DIV/0! message in an averaged cell. I have the formula entered and until values are entered this error is displayed. It is ugly!!! Can someone please tell me the secret.

Thanks in advance as this website has not failed me yet!

Love you guys! Go TITANS
mrstfb in Tennessee

 

[xlhelp]

=if(iserror(average(your_range),"",average(Your_range))

HTH

Indu

 

[MrsTFB]

where do i put this code? In a module? to be run with the other tests? Sorry for my ignorance.

mrstfb

 

[nberryman]

It's a formula so replace your forumla that is giving the error with the example above.

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[MrsTFB]

I thought that, tried it and it gave me an error message on the quotes. I should mention that i still use 97. If that is important.

What could i be doing wrong?

mrstfb

 

[MrsTFB]

I typed this:

=IF((iserror),"",AVERAGE(B5:F5))

I get #NAME?

what is wrong?

 

[GlennUK]

You should have typed

=IF(ISERROR(AVERAGE(B5:F5)),"",AVERAGE(B5:F5))

Glenn.

 

[MrsTFB]

Perfect!!!!! You get a star!!!

Thanks for your patience with me. I have another post with this same workbook about selecting and testing ranges. You could look at that if you are simply passing time.

Thanks again!!!

mrsTFB in Tennessee

 

[scat]

or you could array enter(type in the formula and then press ctrl+shift+enter simultaneously instead of just the enter key) =IF(A1:A5,AVERAGE(A1:A5),0)and format the cell as 0;-0; to get rid of the zero. not sure how an array formula compares to four function calls efficiency-wise.

also, while this probably doesn't matter, and while xlhelp's solution works fine, be aware that if you use "" instead of 0 as the false condition and all the cells to be averaged are blank, "" will mean the result is formatted as text, not a number. this is fine except if you want to include the result in any arithmetical operation - it will return the #VALUE! error.