I need help with the below formula. Each time when there is no data available for specific day, I have the #DIV/0! error.
My formula is as follows:
=IF(B$14<=MAX('BI Raw Data'!$B:$B),AVERAGEIFS('BI Raw Data'!$Z:$Z,'BI Raw Data'!$AM:$AM,"Yes",'BI Raw Data'!$B:$B,'CC - TABLES'!B$14),)
I tried to get rid of this error with the IFFERROR function but without success:
=IFERROR(B$14<=MAX('BI Raw Data'!$B:$B),AVERAGEIFS('BI Raw Data'!$Z:$Z,'BI Raw Data'!$AM:$AM,"Yes",'BI Raw Data'!$B:$B,'CC - TABLES'!B$14),0)
or
=IFERROR(B$14<=MAX('BI Raw Data'!$B:$B),AVERAGEIFS('BI Raw Data'!$Z:$Z,'BI Raw Data'!$AM:$AM,"Yes",'BI Raw Data'!$B:$B,'CC - TABLES'!B$14),"0")
Can you please advise how to rectify this error?
Thanks in advance.
Put the IFERROR around the AVERAGE:
=IF(B$14<=MAX('BI Raw Data'!$B:$B),IFERROR(AVERAGEIFS('BI Raw Data'!$Z:$Z,'BI Raw Data'!$AM:$AM,"Yes",'BI Raw Data'!$B:$B,'CC - TABLES'!B$14),0),)