Use conditional formatting when cell has divide by zero error indicator

The cell returns a divide by zero error indicator because the data for completing the percentage of participants does not have any associated dates. So in order to "hide" the divide by zero error indicator, the iferror function is used, =IFERROR(COUNTIFS('2010 Patients Data Entry'!$X$3:$X$402,"Yes",'2010 Patients Data Entry'!$F$3:$F$402,"<=3/31/2010")/COUNTIF('2010 Patients Data Entry'!$F$3:$F$402,"<=3/31/2010"),""). Conditional formatting has been applied to the cells to see which percentages meet the benchmarks. The cell returns an incorrect conditional format when there are no dates prior to 3/31/2010 and the iferror function is used. If the iferror function is deleted from the formula the conditional formatting does not appear. Is there a way to conditionally format a cell when a divide by zero indicator, #DIV/0!, is returned in a cell?
 

Question Info


Last updated July 17, 2019 Views 3,970 Applies to:
Answer
Answer
On Wed, 14 Jul 2010 13:14:59 +0000, R W Basenji wrote:
 
>The cell returns a divide by zero error indicator because the data for completing the percentage of participants does not have any associated dates. So in order to "hide" the divide by zero error indicator, the iferror function is used, =IFERROR(COUNTIFS('2010 Patients Data Entry'!$X$3:$X$402,"Yes",'2010 Patients Data Entry'!$F$3:$F$402,"<=3/31/2010")/COUNTIF('2010 Patients Data Entry'!$F$3:$F$402,"<=3/31/2010"),""). Conditional formatting has been applied to the cells to see which percentages meet the benchmarks. The cell returns an incorrect conditional format when there are no dates prior to 3/31/2010 and the iferror function is used. If the iferror function is deleted from the formula the conditional formatting does not appear. Is there a way to conditionally format a cell when a divide by zero indicator, #DIV/0!, is returned in a cell?
 
You can use the ERROR.TYPE function:
 
To conditionally format A1, you would use:
 
=ERROR.TYPE(A1)=2
 
 
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.