Conditional Formatting for 100% does not work

0
votes

What if I want to colour the cells without using the icon sets for percentages? I have a very complex formula built in a cell:

=IF($C4="Not Defined","Not Defined",IF(COUNTIFS('Data INC (Q1+04+05._2016)'!$Q:$Q,$B4,'Data INC (Q1+04+05._2016)'!$G:$G,I$3,'Data INC (Q1+04+05._2016)'!$AP:$AP,$I$1)=0,"No incidents",CONCATENATE(IFERROR((TEXT(ROUNDUP(((COUNTIFS('Data INC (Q1+04+05._2016)'!$Q:$Q,$B4,'Data INC (Q1+04+05._2016)'!$G:$G,I$3,'Data INC (Q1+04+05._2016)'!$AP:$AP,$I$1,'Data INC (Q1+04+05._2016)'!$AV:$AV,TRUE))/(COUNTIFS('Data INC (Q1+04+05._2016)'!$Q:$Q,$B4,'Data INC (Q1+04+05._2016)'!$G:$G,I$3,'Data INC (Q1+04+05._2016)'!$AP:$AP,$I$1))),2),"0.00%")),""))))

For this, I have a 3 criteria conditional formatting: between 0 and 0.75 percentages turn to red between 0.75 and 0.95 percentages turn to yellow between 0.95 to 1 percentages turn to green

The conditional formatting for 100% does not work, it turns to red. The conditional formatting only works if I skip the percentage from the formula and turns the value between 0 and 1. I tried the cell format to number, percentage, general, custom and does not work.

1
excelconditional-formatting
Colour the cells? What cells? Are you referring to Excel? Or Google Sheets? Or what? - Pang
Excel 2010. Cells with values. The values are calculated from the above formula I use in the cells of Excel. - DataPower
Are you checking whether the cell equals 1 or 100? It should be 1 for 100%. - Darren Bartrup-Cook
Without trying to convert the value to percentage, the conditional formatting is working with the range from 0 - 1, with the red, yellow and green - even for the 100%. Every case I try to convert the value to percentage, the green part of the conditional formatting is corrupted. - DataPower
The main problem is that if I get the value in the range 0-1, I cannot modify the number format to percentage. So I tried to incorporate some converting to the big formula, but then the conditional formatting does not work for the 100%. - DataPower

1 Answers

1
votes

This assumes your formula is returning a correct value.

Your conditional formatting is testing for a numeric value. Your formula is returning a text string. And your description of how you have set up your conditional formatting is not clear.

  • Remove the TEXT function from your formula so it returns a numeric value.
  • Format the cell to percent with two decimal places.
  • If the conditional formatting still does not work, describe exactly how you have it set up, using the terms in the dialog boxes, and exactly what is contained there (or screen shots if you can).