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

#### Renaissance

##### Board Regular
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Wrap an iferror around your formulas and that will get rid of your errors. Then you can just remove 0 from your averages.

hi,

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

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.

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

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

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

Code:
``=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 -_- )

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.

Replies
5
Views
330
Replies
9
Views
441
Replies
19
Views
2K
Replies
1
Views
279
Replies
8
Views
658

### Forum statistics

1,221,053
Messages
6,157,640
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back