How to make #DIV/0! Invisible?!

[LaurenHancy]

Hello,

I am using this formular to find the average for a certain products but it comes back with a DIV/0!. I still want the formular to be in the back ground as when i rerun the report it may come back with a figure.

This is the current formular i am using:

{=AVERAGE(IF($J$20:$J$201=B5,$AI$20:$AJ$201))}

Hope you can help.

Many thanks

Lauren

 

[Stu Dapples]

Try this:

{=IF(ISERROR(AVERAGE(IF($J$20:$J$201=B5,$AI$20:$AJ$201)),"",AVERAGE(IF($J$20:$J$201=B5,$AI$20:$AJ$201))}

Should do the trick but never used with the curly braces before...

 

[LaurenHancy]

Thanks for the fast reply. Unfortunately it doesn't work. It doesnt even bring a error message back, just:

{=IF(ISERROR(AVERAGE(IF($J$20:$J$201=B5,$AI$20:$AJ$201)),"",AVERAGE(IF($J$20:$J$201=B5,$AI$20:$AJ$201))}

Any other ideas?

Thanks

L

 

[Stu Dapples]

Dont type in the curly brace {}, I'm not sure why you need to use them anyway but if you do, enter the formula then hold ctrl, shift and hit enter, excel will add the braces for you!

I think it is something to do with the way in which excel handles the formula, something about iterations

I think you formula will work without, give it a try....

 

[Jonmo1]

You're missing a couple ))

try

=IF(ISERROR(AVERAGE(IF($J$20:$J$201=B5,$AI$20:$AJ$201))),"",AVERAGE(IF($J$20:$J$201=B5,$AI$20:$AJ$201)))

enterd with CTRL + SHIFT + ENTER

 

[LaurenHancy]

Thats worked a treat, many thanks for all your help guys.

Lx