Using averageif when all cells are 0's

[Chrisaep]

Hi,

This issue is probably best shown with an example. If a range of ,say, 4 cells show 1, 2, 3, and 0, I want the formulated cell to show the average excluding the 0, which I use the averageif function for and it works great (this is the formula I use: =averageif(T12:T15,"<>0"); which equals 2 in this case.

My problem is that occasionally all 4 of these cells will be 0 which results in the formulated cell showing #DIV/!. What I need is for this formulated cell show a 0 in this situation but still apply the above formula when this isn't the situation. The reason for this is the number that this formulated cell produces is part of another formula, so for obvious reasons I can't have #DIV/! showing.

I've attempted to play around with combining averageif, OR and IF functions but I haven't been able to figure it out. I've attached an example to help explain.

Any help will be greatly appreciated (and hope all this makes sense!)

Thanks.

[BB1972]

Chris, welcome to the forum.

You just need to wrap an "ISERROR" around your formula, which you can tell to return 0 if the formula evaluates to an error or div/0.

Please see attached, which I hope helps.

[Chrisaep]

Perfect! Thank you so much, this has been a really big help.

[BB1972]

No problem - glad to help. Can you please mark this thread as "SOLVED" using the thread tools, provided you're satisfied that it is?

Thank you.