Google Sheets: Ignore Errors and Calculate Average

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!

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.

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.

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

How are you using AVRAGEIFS in Excel 2013?

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

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

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

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?

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.

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

Yes, Algiw

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).