approximately formula by %

[Alenmabb]

greetings all,

i have fixed numbers on column A
and numbers on column B.


my question is.. how can i using formula that said
1-(if the number on B1 grater than number on A1 by 3%) result will be colored red.

3--(if the number on B1 smaller than number on A1 by 3%) result will be colored red.


2-(if the number on B1 same as number on A1 result will be colored green.


so how can coloring the column B by percentage??? 3 colors!




complex yeah ^^

thanks

 

[gaz_chops]

Welcome to Mr Excel

[TABLE="width: 261"]
<tbody>[TR]
[TD="width: 87, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]103[/TD]
[TD="width: 87, align: right"]100[/TD]
[TD="width: 87"]Red[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]97[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]98[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]102[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]102[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
</tbody>[/TABLE]

highlight the column of data that you want to apply the formatting to

In cond formatting enter following formula

=A1=B1
select the colour that you want, tick "Stop if True"

add another rule, enter formula
AND(A10<=B10*1.03,A10>=B10*0.97)
select the colour that you want

Click "OK"

 

[Alenmabb]

Welcome to Mr Excel

[TABLE="width: 261"]
<tbody>[TR]
[TD="width: 87, align: right"]103[/TD]
[TD="width: 87, align: right"]100[/TD]
[TD="width: 87"]Red[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
</tbody>[/TABLE]

highlight the column of data that you want to apply the formatting to

In cond formatting enter following formula

=A1=B1
select the colour that you want, tick "Stop if True"

add another rule, enter formula
AND(A10<=B10*1.03,A10>=B10*0.97)
select the colour that you want

Click "OK"

thank gaz, appreciated

but the red formula did not work. see the image.

the formula convert to this
="(A10<=B10*1.03,A10>=B10*0.97)"

and i try this also

AND(A10<=B10*1.03,A10>=B10*0.97)

nothing work>




https://imgur.com/a/uYlbLFe

 

[gaz_chops]

Try

AND(A1<=B1*1.03,A1>=B1*0.97)

for the 2nd formula

 

[Alenmabb]

Try

AND(A1<=B1*1.03,A1>=B1*0.97)

for the 2nd formula

did not work.


i do appreciate if you attach a file.

thank