Expression not working as expected

[soniasweety]

I have used below logic but its showing me wrong result, 

how to frame this logic,

logic: 

<97 or >103--red

100-green

>=97 and <100 or >100 and <=103 --yellow

used Expression: Expression says ok but result getting wrong colurs

=IF(Num([Rate],'#,##0.00%')<'97.00%' and Num([Rate],'#,##0.00%')>'103.00%',red() ,
IF(Num([Rate],'#,##0.00%')>='97.00%' and Num([Rate],'#,##0.00%')<'100.00%' and
Num([Rate],'#,##0.00%')>'100.00%' and Num([Rate],'#,##0.00%')<='103.00%',Yellow(),Green()))

[neelamsaroha157]

Understanding that you want to have red color when the value is greater than 103%, yellow if value is between 97% & 103% and green if the value is less than 97% -

Try this - 

if(Num([Rate],'#,##0.00%')>'103.00%',red(),

if(Num([Rate],'#,##0.00%')>'97.00%',Yellow(),
Green()))

I would also suggest to use the absolute numbers like .97 for 97%, it just makes the code look neat.

[Colin-Albert]

Your ands and ors are mixed up.

 Try this

=IF(Num([Rate],'#,##0.00%')<'97.00%' or Num([Rate],'#,##0.00%') >'103.00%',red() ,
IF(Num([Rate],'#,##0.00%')>='97.00%' and Num([Rate],'#,##0.00%')<'100.00%', Yellow(),
IF(Num([Rate],'#,##0.00%') >'100.00%' and Num([Rate],'#,##0.00%') <='103.00%', Yellow(),

Green() )))

The rate cannot be <97% and > 103%

[soniasweety]

No.
i want to show
1. exact 100% means green
2. <97 or >103 red
3. >=97 and <100 or >100 and <=103 --yellow

[soniasweety]

thanks colin .

but still  99.99% showing green only its should be yellow

[neelamsaroha157]

Okay, then try this -

if(Num([Rate],'#,##0.00%')='100%',Green(),

if(Num([Rate],'#,##0.00%')>'103.00%' OR Num([Rate],'#,##0.00%')<'97.00%',red(),
Yellow()))

[Colin-Albert]

Your expression is based on 2 decimal places so is actually calculating as 

1. exact 100.00% means green
2. <97.00 or >103.00 red
3. >=97.00 and <100.00 or >100.00 and <=103.00 --yellow,

so 100.01% will show as yellow - Are you sure you are working to this level of precision?

[soniasweety]

Yes its exactly 100% means it should show green

[soniasweety]

your expression is showing 100.00% also yellow

[soniasweety]

hi Colin_albert

Neelamsaroha157

atatching the sample