Formula based on the condition to format with color

[Monty]

Hello Everyone!

Excel file attached!

A very quick one based on condition in "Status" column greater then or less then need to show in Threshold column with "Yes" or "No" with color.

Here the challenge is need to compare from status to "Percentage" column.

For example:

for the first line Rev < 20% when compare to percentage 27% which is greater so need to fill with "No" with green color.

A Quick screen short for your reference.

 

[Chihiro]

Never a good idea to use text as condition.
I'd recommend splitting it in to 2 or more columns.

 

[Monty]

Hello Chihiro sir.
Good approach...But Unfortunately I can't use helper column in the sheet due to some restrictions..
So looking for solution with available columns only with colors.

 

[Chihiro]

Then use below formula.
=IF(COUNTIF(B2,SUBSTITUTE(SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND(LOOKUP(2,1/FIND({"<",">"},A2),{"<",">"}),A2)+1)," ",""),"bps",""))>0,"Yes","No")

 

[bosco_yip]

Another option,

1] In C2, formula copy down :

=COUNTIF(B2,SUBSTITUTE(MID(A2,MIN(FIND({"<",">"},A2&"<>")),99),"bps",""))

2] Then, check Conditional Formatting and Custom Format formula used as per attached file

Regards
Bosco

 

[Monty]

Fantastic!