Getting rid of #DIV/0! in Average function

[Waylor]

I'm trying to make an interactive sheet which requires input from other sheets. I've got the formula for filling the data from other sheets working fine. But trying to make my 'dashboard' work has been tricky (I'm a fiddler, not an expert!)

Attached is a picture where I've been able to get one of my averages to work where it ignores cells with 0% in them (D4), but when that cell is empty again and all the values are 0%, it shows '#DIV/0!' when I'd like it just to say 0% like D12 (this one is just =B12).

D18 & D25 are examples of what I mean. How do I fix this? Have wasted too much time trying formula's searching google

Thanks!

 

[Scott Huish]

=IFERROR(yourformula,0)

 

[Waylor]

=IFERROR(yourformula,0)

What am I doing wrong? I can't seem to add in the ',0' that you've included without causing an error. The closest I can get is this, and it displays 'TRUE'

 

[awoohaw]

I think you just need this:

=IFERROR(AVERAGEIF(B4:B9,"<>"0,B4:B9),0)
You may not need the AVERAGE IF at all.
Just

=IFERROR(AVERAGE(B4:B9),0)

may get what you want as well.

Book1
	A	B	C
1
2
3
4		50
5		55
6		0
7		60
8		65
9		70
10
11		60
12	or:	50
13
Sheet5

Cell Formulas
Range	Formula
B11	B11	=IFERROR(AVERAGEIF(B4:B9,"<>0",B4:B9),0)
B12	B12	=IFERROR(AVERAGE(B4:B9),0)

 

[Waylor]

I think you just need this:

=IFERROR(AVERAGEIF(B4:B9,"<>"0,B4:B9),0)
You may not need the AVERAGE IF at all.
Just

=IFERROR(AVERAGE(B4:B9),0)

may get what you want as well.

Book1
	A	B	C
1
2
3
4		50
5		55
6		0
7		60
8		65
9		70
10
11		60
12	or:	50
13
Sheet5

Cell Formulas
Range	Formula
B11	B11	=IFERROR(AVERAGEIF(B4:B9,"<>0",B4:B9),0)
B12	B12	=IFERROR(AVERAGE(B4:B9),0)

That got it! Thanks! (I had to change the "<>"0 part to "<>0")

 

[awoohaw]

That got it! Thanks! (I had to change the "<>"0 part to "<>0")

Sorry, about that, I had it correct in the mini sheet..