CODEWITHSUNDEEP
excelexcel-formula
kmathers
kmathers

Reputation: 1

Average formula results while skipping errors

I am trying to average the results of the ratios of three sets of sumifs formulas referencing three different years data on three separate sheets. i.e. I want my formula to be average(formula 1, formula 2, formula 3). The problem is for part of my data set formula 1 or formula 3 might throw an error because there's only data of that type on one or two of the years. So I want to exclude those averages only the years that have data. Solutions I've found online to this problem such as average(if(iserror don't work because I'm not averaging an array. I tried this and it returns #VALUE! and I can't for the life of me figure out why:

=AVERAGE(IFERROR((SUMIFS('2014 Data'!$T:$T,'2014 Data'!$F:$F,'Az Diamondbacks'!$D$1,'2014 Data'!$V:$V,'Az Diamondbacks'!J$3,'2014 Data'!$L:$L,'Az Diamondbacks'!$B4)/SUMIFS('2014 Data'!$T:$T,'2014 >Data'!$F:$F,'Az Diamondbacks'!$D$1,'2014 Data'!$V:$V,'Az Diamondbacks'!J$3)),""),IFERROR((SUMIFS('2015 Data'!$T:$T,'2015 Data'!$F:$F,'Az Diamondbacks'!$D$1,'2015 Data'!$V:$V,'Az Diamondbacks'!J$3,'2015 Data'!$L:$L,'Az Diamondbacks'!$B4)/SUMIFS('2015 Data'!$T:$T,'2015 >Data'!$F:$F,'Az Diamondbacks'!$D$1,'2015 Data'!$V:$V,'Az Diamondbacks'!J$3)),""),IFERROR((SUMIFS('2016 Data'!$T:$T,'2016 Data'!$F:$F,'Az Diamondbacks'!$D$1,'2016 Data'!$V:$V,'Az Diamondbacks'!J$3,'2016 Data'!$L:$L,'Az Diamondbacks'!$B4)/SUMIFS('2016 Data'!$T:$T,'2016 Data'!$F:$F,'Az Diamondbacks'!$D$1,'2016 Data'!$V:$V,'Az Diamondbacks'!J$3)),""))

Upvotes: 0

Views: 29

Answers (1)

barry houdini
barry houdini

Reputation: 46341

When you put the values directly in to AVERAGE function then the "null string" "" returned by IFERROR forces an error.

Try using CHOOSE function to supply an array to the AVERAGE function, e.g.

=AVERAGE(CHOOSE({1,2,3},IFERROR(SUMIF1,""),IFERROR(SUMIF2,""),IFERROR(SUMIF3,"")))

replace SUMIF1, SUMIF2 and SUMIF3 with your three formulas or cell references containing those formulas

Upvotes: 2

Related Questions