Conditional Formatting and DIV/0 Error

[paulstan]

Hi

I have 3 conditional formatting statements that change the colour of a cell based on the total, ie 0-50 (cell colour to RED); 51-90 (cell colour to AMBER); 90+ (cell colour to GREEN). This works, no problem; however, it is possible that the cell could contain a DIV/0 error message and I want to ensure that the message is hidden from view.

I would normally put an ISERROR conditional formatting statement to sort the problem out but, based on the above, 3 statements already exist. Therefore, will I have to create VB code to get around this, or can I add ISERROR to each of the 3 conditional formatting statements.

Thanks for taking the time to read.

Regards

Paul S

 

[Aladin Akyurek]

How did you get that error, that is, what is the formula that produces that error?

 

[shg]

You could add the font color to the CF, and then make the default font color white.

 

[Scott Huish]

Why not fix the formula that generates the #DIV/0! error, which won't require ISERROR.


Basic formula:

=IF(B1,A1/B1,"")

 

[paulstan]

Aladin

My formula would be dividing one cell by another. Spreadsheet is based on a timesheet, so if someone doesn't work on a particular day, then their number of hours worked would be 0, which means their production for the day would also be 0, which gives me the DIV/0 error.

shg

The default font colour would need to be black (so as to read the percentages against each colour), but the DIV/0 error font needs to be white.

HOTPEPPER

Would I then need to change Cell Values to Formula Is or do I add the code to Col G (col F would be hidden)?



Hope the simplistic diagram below helps

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">hours</TD><TD style="TEXT-ALIGN: center">produced</TD><TD style="TEXT-ALIGN: center">hidden</TD><TD style="TEXT-ALIGN: center">%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Cond Format statement</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">0.714285714</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffc000">71.00%</TD><TD style="TEXT-ALIGN: right"></TD><TD>Cell value Between =0.5 and =0.9</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">100.00%</TD><TD style="TEXT-ALIGN: right"></TD><TD>Cell value Greater than =0.9</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0.375</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">38.00%</TD><TD style="TEXT-ALIGN: right"></TD><TD>Cell value Between =0 and =0.5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">0.8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffc000">80.00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">#DIV/0!</TD><TD style="TEXT-ALIGN: right">#DIV/0!</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">formula</TD><TD style="TEXT-ALIGN: center">formula</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">col E divided by col D</TD><TD style="TEXT-ALIGN: center">ROUND Col F,2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>


Thank you all for your contributions

Paul S

 

[Aladin Akyurek]

In F16 enter and copy down:

=IF(D16,E6/D16,0)

 

[Haseeb Avarakkan]

Hello, Use IFERROR.

F16, copy down.

=IFERROR(E16/D16,0)

This will give 0, if the result is any error.

 

[shg]

The default font colour would need to be black (so as to read the percentages against each colour), but the DIV/0 error font needs to be white.

Again, the font color (black or automatic) can be part of CF.

 

[paulstan]

Aladin & Haseeb

Many thanks. Both those formulas worked.

And a big thanks to all those that contributed.

Regards

Paul S