Prevent a DIV/0 in an averaging formula

[DHBarkley]

Good day,

Here is the formula I am working with that is returning a DIV/0 value when there is no data to currently average. I would like it to return a 0 value.

=AVERAGEIF('[2013 Pac West Master Tracker 1-1-13 - v8.2.xlsb]PAC West'!$A$14:$A$5636,"Z2",'[2013 Pac West Master Tracker 1-1-13 - v8.2.xlsb]PAC West'!$BU$14:$BU$5636)

If the target range has a value I get a correct response, if it does not have a value I get DIV/0. I need the response to be a 0 if the target range has no values.

I hope made myself clear.

Thanks,

DHBarkley

[Ace_XL]

Wrap in IFERROR

=IFERROR(AVERAGEIF('[2013 Pac West Master Tracker 1-1-13 - v8.2.xlsb]PAC West'!$A$14:$A$5636,"Z2",'[2013 Pac West Master Tracker 1-1-13 - v8.2.xlsb]PAC West'!$BU$14:$BU$5636),0)

[liz5818]

you will want to add another if function.
=if('[2013 Pac West Master Tracker 1-1-13 - v8.2.xlsb]PAC West'!$A$14:$A$5636>=0,AVERAGEIF('[2013 Pac West Master Tracker 1-1-13 - v8.2.xlsb]PAC West'!$A$14:$A$5636,"Z2",'[2013 Pac West Master Tracker 1-1-13 - v8.2.xlsb]PAC West'!$BU$14:$BU$5636),0)

try this and see if it will work. In theory, if the range has a value of 0 or higher, then it should run the averageif function. If it doesn't, then it should come up with a 0.

[DHBarkley]

Thank you Ace, that correct the problem.