Conditional Format: Change colors for dif. values not workin

[Kwest]

Hi,

I’ve been checking the topics for conditional formatting answers to my problem and I have not found one to meet my needs. Here is what I am trying to do…

I am calculating service levels in number of minutes my team took to resolve an issue. I am trying so setup conditional formatting so that if the number of minutes is lower than the goal the cell will color green and if higher than the goal it will color red. Of course there are several different types of issues that are resolved so there are different numbers of minutes for each goal. I calculated the goal by using a text to value formula. Here is that example: Cell E4 =IF(D4="other","1440", IF(D4="Hardware","2880", IF(D4="Passwords/Logon IDs", "720", IF(D4="Telephone", "1440", IF(D4="PBX", "1440", IF(D4="Application", "1440"))))))

So let’s say that E4 shows up as 1440. Cell C4 is the total number of minutes it took to resolve the problem. I set up conditional formatting to show criteria 1 as “cell C4 is less than E4 = Green” and criteria 2 as “cell C4 is greater than E4 = Red”. Sounds easy enough…but it doesn’t work. Therein lies the problem… If I try to drag that format down the sheet it wants to use cell E4 only as the base number to measure against. So…I found a formula and tried to set up conditional formatting like this: criteria 1 =IF(C4<E4,1,0) “green” and criteria 2 =IF(C4>E4,1,0) “red”. That works great for dragging as it changes the cell numbers to provide the base number to measure against. But…the color is not changing to “red” when I get down to a point where the cell value is higher. Cell C15 is 2890 and E15 is 2880. C15 should show “red” but is showing “green”. I checked conditional formatting for that cell and it is using the proper cells. Is this happening because of the crazy IF formula that I am using in the E column? Is there a better way? Thanks!!

 

[Kwest]

Re: Conditional Format: Change colors for dif. values not wo

I can see that the lower formula did not show up right. Here it is:

So…I found a formula and tried to set up conditional formatting like this: criteria 1 =IF(C4<E4,1,0) “green” and criteria 2 =IF(C4>E4,1,0) “red”.

 

[Kwest]

Re: Conditional Format: Change colors for dif. values not wo

I can see that the lower formula did not show up right. For some reason it does not like my less than sympol. So here it is:

So…I found a formula and tried to set up conditional formatting like this:

criteria 1 =IF(C4 is less thand E4,1,0) “green”
criteria 2 =IF(C4>E4,1,0) “red”.

 

[Kwest]

Re: Conditional Format: Change colors for dif. values not wo

Okay...I'm a dummy. I figured it out!! The key to this one is that in my "IF" statement I was returning the values as text and not a number. This is because I was using the quotation marks around the value. For example this is what I was using:

=IF(D4="other","1440") and I changed it to
=IF(D4="other",1440) and it works!!

Woohoo! Thanks for looking!