Conditional Formatting / Formula help

[asajnani]

Hello there,

I am trying to use a format and conditional format to see if a target has been achieved. The formula / conditional format seems to work when there are number in the cells it is referring to, however, when the cells the formula refers to contains texts, it marks it as the target has been achieved but is really should display an "X". Any idea how I can get around this?

File attached - any help or advice would be much appreciated!


Thanks in advance,
A

 

[Khalid NGO]

Hi,

Your cells A2:B2 are stored as TEXT, first convert these cells into real numbers, then use the following in J2:

=IF(AND(B2>C2,ISNUMBER(B2)),1,IF(AND(B2>=(C2-(C2*0.1)),ISNUMBER(B2)),2,IF(AND(B2<(C2-(C2*0.1)),ISNUMBER(B2)),3,"N/A")))

Regards,