問Excel conditional formatting

請問可唔可以做到
將格內數值同0.25的倍數的相差在0.05以內的數值用相差值做color scale?
即係例如
0.25係綠色
0.02係黃色
1.95係紅色
1.12係白色(無formatting)
如果唔得嘅話可唔可以只係highlight數值同0.25的倍數相差少於0.05的格?
謝謝

color scale 無得紅黃錄白,因為最多3隻色
set conditional formatting with formula
something like
  1. =(A1*4-INT(A1*4))<0.2
複製代碼
A1你要set conditional的最左上cell

TOP

多謝解答
我意思係如果不在範圍內就唔format
唔知做唔做到?

TOP

https://support.office.com/en-za ... ;rs=en-ZA&ad=ZA

formula用我上邊個條
如果你套落A2 至 A7
formula係
  1. =(A2*4-INT(A2*4))<0.2
複製代碼

TOP

係唔係即係唔可以formula同colorscale一齊用?
而家用左呢D formula
  1. AND((MROUND(D5,0.25)-D5)<=0.01,(MROUND(D5,0.25)-D5)>=-0.01,NOT(D5=0))
複製代碼
  1. OR(AND((MROUND(D5,0.25)-D5)>0.01,(MROUND(D5,0.25)-D5)<=0.02,NOT(D5=0)),AND((MROUND(D5,0.25)-D5)<-0.01,(MROUND(D5,0.25)-D5)>=-0.02,NOT(D5=0)))
複製代碼
試過用你個formula無format到少過0.25的倍數0.05以內的case

TOP

my bad, updated
  1. =OR((A1*4-INT(A1*4))<0.2,(A1*4-INT(A1*4))>0.8)
複製代碼

TOP

我已經用我的formula搞掂左
不過都多謝師兄幫忙
同埋如果用formula係唔係只可以出單色?

TOP

color scale唔比用relative reference, 我地d formula廢晒武功
set幾條formula幾條rule囉

TOP

唯有係咁做
多謝師兄幫手

TOP