Can't figure out the right Conditional Formatting rule

[robvdbrand]

Hi all,


I am working on a variance analysis file for my company comparing new versus old prices (2013 versus 2012). Using conditional formatting, I want to highlight variances larger than |50%|.


The formula I am using to calculate the variance is:


=IFERROR((E2-F2)/F2,"no data")


where E2 = new price & F2 = old price


I use the IFERROR formula and corresponding "no data" result to make the file look nicer when column E2 doesn't display a new price (this will happen as it's possible to use the file for only part of the product portfolio).


The conditional formatting rules I am using are:


Cell value > 50% --> format to red fill/white font

Cell value < -50% --> format to red fill/white font


This works fine, however whenever the cell contains "no data" instead of a value, it is also formatted to red fill/white font.


Which rule could I add so that "no data" is formatted in the same way as normal cells? Or should I use different rules altogether?


Thanks in advance for any help!


Rob

 

[Faseeh]

Hi robvdbrand,


Can you upload the sample file??

 

[sgmpatnaik]

@rodvdbrand


Hi


as per my understanding your result will be shown in g2 and you are using the formula in


conditionar formating as


=IFERROR((E2-F2)/F2,"no data")


Cell value > 50% --> format to red fill/white font

Cell value < -50% --> format to red fill/white font


create another rule as


specific text= containing = no data and just pres ok


when the result shown as no data then it will in normal mode


Hope it will help


Other wise please give some more details and better to upload a sample work book


Thanks


SP

 

[robvdbrand]

@Faseeh, @SP,


Thanks for your replies. Find below the link to a sample workbook:


https://www.dropbox.com/s/bgcdlgbkjliffik/Sample%20Conditional%20Formatting.xlsx

 

[NARAYANK991]

Hi ,


The formula you are using : =IFERROR((E2-F2)/F2,"no data") , will generate an error only if F2 is 0.


Secondly , if you just want to display "No Data" under this condition , you can always format the cell to display that , and your formula can be changed to =IF(F2=0,0,(E2-F2)/F2) , so that it works even in Excel 2003 , where the IFERROR function is not available.


Thirdly , if you are really using percentages , you can always use the ABS function , and a threshold value of 0.5 ( 50 % ) as in : =IF(F2=0,0,ABS((E2-F2)/F2))>0.5 as the CF formula.


Narayan

 

[robvdbrand]

Thanks everyone for your suggestions..It prompted me to have another look myself. The solution was very simple.


I added one rule:


Cell Value = "no data" --> format normally


I must have done something wrong the first time I tried this..