# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  Google Sheets: Ignore Errors and Calculate Average

## Manikandan Arumugam

Hi All,

I have formula that pulls data from multiple sheets and It provides numbers and errors values. I am looking the formula that will ignore the errors and calculate the average. Here's my formula

=AVERAGEIFS(A!B1:B2,A!A1:A2,Data!A1)+AVERAGEIFS(B!B1:B2,B!A1:A2,Data!A1)+AVERAGEIFS('C'!B1:B2,'C'!A1:A2,Data!A1)

Output:

=#DIV/0!+0+100

I would like to calculate the average for above mentioned values.

Expected Output

50

Please do the needful. Thanks in advance!

----------


## AliGW

Office 2010 does not have AVERAGEIFS, so you need to update your forum profile, please.

There are instructions at the top of the page explaining how to attach your sample workbook.

----------


## hrlngrv

If there are no numbers in a range, any AVERAGE function will return #DIV/0!. If there are no numbers in all 3 ranges, I'd argue you should produce #DIV/0!. As long as there's one or more number in any of the ranges, try

*[CORRECTION]*

=SUM(SUMIFS(A!B1:B2,A!A1:A2,Data!A1,A!B1:B2,{"<0";">0"})+SUMIFS(B!B1:B2,B!A1:A2,Data!A1,B!B1:B2,{"<0";">0"})+SUMIFS('C'!B1:B2,'C'!A1:A2,Data!A1,'C'!B1:B2,{"<0";">0"}))
/SUM(COUNTIFS(A!A1:A2,Data!A1)+COUNTIFS(B!A1:A2,Data!A1)+COUNTIFS(C'!A1:A2,Data!A1))

Sorry. Sloppy job of copy/paste without enough editing.

----------


## Manikandan Arumugam

Thanks for your time, Mate. It shows me error on this argument "COUNTIFS(A!B1:B2,A!A1:A2,*Data!A1*)". Can you please help me

----------


## AliGW

How are you using AVRAGEIFS in Excel 2013?  :Confused:

----------


## Manikandan Arumugam

Hi Aligw,

I am using Excel Office 2013 only and it works. Confused of your replies. . Please help me with my query. Correct me if I am wrong

----------


## AliGW

Please see here: https://support.office.com/en-gb/art...8-f7c5c3001690

Excel 2013 is not listed.

Only AVERAGEIF is available in Excel 2013:

https://support.office.com/en-gb/art...9-f5576d8ac642

----------


## Manikandan Arumugam

Hi Aligw,

Ya in microsoft website i could not able to see it. But i could see that its available in another website. As i said it works well for me.

Attachment 680541

=average(#DiV/0!,0,100)

How to ignore error from this and calculates average? plz help me

----------


## AliGW

Ah - thanks for clarifying.

The problem you have is that any part of the formula that returns an error will mess up the formula. You need to decide how you want errors handling. Converting them to 0 isn't desirable, because it will mess up your averages, and returning text will generate a #VALUE! error.

So, to be honest, you have a bit more thinking to do. How many sheets do you need to test? Will it only ever be three?

----------


## Manikandan Arumugam

Hi Aligw,

Thanks for your time. I have around 22 sheets. Actually I have found Aggregate formula which will skip error values. But I could not able to use that in G Drive.

----------


## AliGW

So are you now saying this is for Google Sheets???

----------


## Manikandan Arumugam

Yes, Algiw

----------


## AliGW

OK - so, you posted to the wrong part of the forum and failed to mention this caveat until post #10. 

I will move this thread and update your thread title for clarity (and so as not to waste time).

----------


## hrlngrv

Dunno if this is still open, but I corrected my response above.

----------

