How to apply sheet summary calculations while excluding error values?

C. Powell

Hello,

I'm trying to create On Time Delivery (OTD) metrics using sheet summaries. These work well on the test sheet, even with blanks and missing components, yet when applied to live project sheets the returned values contain #invalid data type for any blank entries or those with a missing component which ultimately blocks the sheet summary calculations.

invalid data.PNG

I've tried to research solutions using CONTAINS, ISDATE, IFERROR but can't find the right application or where to place them.

OTD % formula:

=COUNTIF([Final Variance]:[Final Variance], >=0) / (COUNT([Final Variance]:[Final Variance]))

I don't know how to prevent/exclude cells that return #invalid data type so my sheet summary report consists of the correct % for the test sheet, then a bunch of #invalid data types below that

OTD sheet summary report.PNG

The final variance formula itself is below but might in fact be the root problem needing some attention:

=DATEONLY([Final delivery]@row) - ([FE: final files delivered]@row)

Thank you in advance for this specific issue and I'm open to any other methods on OTD calculation across hundreds of sheets!

Genevieve P.

Hi @C. Powell

You could wrap an IFERROR statement around your OTD% formula to return a blank cell if there's an error:

=IFERROR(COUNTIF([Final Variance]:[Final Variance], >=0) / (COUNT([Final Variance]:[Final Variance])), "")

Or actually, you could wrap this same function around your Final Variance formula which should then resolve the issue with the other formula:

=IFERROR(DATEONLY([Final delivery]@row) - ([FE: final files delivered]@row), "")

Let me know if this works!

Genevieve

C. Powell

Hi Genevieve,

Both worked, thank you!

Genevieve P.

Hi @C. Powell

You could wrap an IFERROR statement around your OTD% formula to return a blank cell if there's an error:

=IFERROR(COUNTIF([Final Variance]:[Final Variance], >=0) / (COUNT([Final Variance]:[Final Variance])), "")

Or actually, you could wrap this same function around your Final Variance formula which should then resolve the issue with the other formula:

=IFERROR(DATEONLY([Final delivery]@row) - ([FE: final files delivered]@row), "")

Let me know if this works!

Genevieve

C. Powell

Hi Genevieve,

Both worked, thank you!

Genevieve P.

Wonderful!! Glad to hear it 🙂

You were right to point out the root cause/formula ... it all trickles through from there haha.