Average range to exclude: 0's, #N/A and #DIV/O!

[Renaissance]

Hey all,

I am trying to average a range of cells, but have occasional 0's, #N/A and #DIV/O! errors that I don't want skewing my data.

I've tried to use averageifs and * to come up with the following, but to no avail:

=AVERAGEIF(D27:O27,"<>0")*AVERAGEIF(D27:O27,"<>#N/A")
=AVERAGEIFS(D27:O27,D27:O27,"<>0",D27:O27,"<>#N/A",D27:O27,"<>#DIV/O!")

I do apologize for my horrible formula writing.

~Rena

 

[NeonRedSharpie]

Wrap an iferror around your formulas and that will get rid of your errors. Then you can just remove 0 from your averages.

 

[Latha]

hi,

Replace 0's, #N/A and #DIV/O! with text "NA".

 

[Renaissance]

Thank you NeonRedSharpie and Latha for your rapid response.

I didn't include this information before because I didn't think it would affect the answer, but the range of cells I am averaging I am using nested formulae to calculate things from other tabular data, all of which are numerical (unless there is a DIV/O or N/A error). And these cells calculate based on a reference, so when I select a different name (for example) it will yield data in reference to that name, so it is ever-changing.

So, if I wrap an IfError function around any of my formulae, it will simply return the "value_if_error" that I choose. I thought that if I had used "*" on multiple averageif functions it would only average the cells that meet criteria of each one, which should exclude the #N/A, #DIV/0! and 0's.

 

[NeonRedSharpie]

No, wrap an iferror around your nested IF statements. That way they will return 0. Then you can use:

=AVERAGEIF($A$1:$A$5,">"&0,$A$1:$A$5)

 

[Renaissance]

No, wrap an iferror around your nested IF statements. That way they will return 0. Then you can use:

=AVERAGEIF($A$1:$A$5,">"&0,$A$1:$A$5)

Well now I feel asinine for overlooking something so simple. I am so sorry and very grateful for your solution Thanks Neon! (I think I need to take a nap -_- )

 

[NeonRedSharpie]

Well now I feel asinine for overlooking something so simple. I am so sorry and very grateful for your solution Thanks Neon! (I think I need to take a nap -_- )

No problem. That's what we're here for. It's always the simplest solutions we end up overlooking. That's why I said "screw it" and write everything in VBA. Plus then I can bill more hours.