I am tracking compliance percentages (2 rows per calendar day). I want a rolled-up matrix of the averaged results for the previous month. I want the average part of the statement to ignore blanks and errors (not column item is required every day) but include zeros (indicates non-compliance). I have tried the formulas below and cannot get the average percentage not to include blank cells but include the zero values as the same time.
=IF((DATA!A:A=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))),"",(AVERAGEIF(DATA!Q:Q,">"&0,DATA!Q:Q)))
=IF((DATA!A:A=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))),"",(AVERAGE(DATA!Q:Q)))
=IF((DATA!A:A=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))),"",(AVERAGEIF(DATA!Q:Q,">"&0,DATA!Q:Q)))
=IF((DATA!A:A=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))),"",(AVERAGE(DATA!Q:Q)))
