Conditional Formatting and Cell Formula Not Cooperating
So I’m trying to format a spreadsheet in excel in which one cell (B92) depends on the subtraction of the two cells above it (B91,B90) The cell (B92) has different sets of ranges/tolerances that depend on another set of ranges from (B91) for example, when B91>=2 and B91<=2.499, then B92>=.011, and B92<=.0165. There are 28 total sets of ranges for B91 and a separate 28 for B92 that depend on B91. I’ve created 3 long formulas for the colors green, yellow, and red. Green is supposed to show when B91-B90= a value within the given ranges of B92. Yellow when it falls on the max and min number. Red when it’s outside of the tolerances. Referring back to the example, when B91>=2 and B91<=2.499, then when B92=.011 or B92=.0165 the color should be yellow. When B92>.011 and B92<.0165 it’s green. When B92<.011 or B92>.0165 it shows red. My equations work when I input the difference by hand, but when I set the equation =(B91-B90) in cell B92 only the first set works properly. I’ve been trying to crack this for a week and have decided I need fresh thoughts. Please help!
(I know now I didn't need 3 AND statements)
GREEN
=IF(AND(AND(AND($B$92>0.01,$B$92<0.015),$B$91>0),$ B$91<2),TRUE,IF(AND(AND(AND($B$92>0.011,$B$92<0.01 65),$B$91>=2),$B$91<=2.499),TRUE,IF(AND(AND(AND($B $92>0.012,$B$92<0.018),$B$91>=2.5),$B$91<=2.999),T RUE,IF(AND(AND(AND($B$92>0.013,$B$92<0.0195),$B$91 >=3),$B$91<=3.499),TRUE,IF(AND(AND(AND($B$92>0.014 ,$B$92<0.021),$B$91>=3.5),$B$91<=3.999),TRUE,IF(AN D(AND(AND($B$92>0.015,$B$92<0.0225),$B$91>=4),$B$9 1<=4.499),TRUE,IF(AND(AND(AND($B$92>0.016,$B$92<0. 024),$B$91>=4.5),$B$91<=4.999),TRUE,IF(AND(AND(AND ($B$92>0.017,$B$92<0.0255),$B$91>=5),$B$91<=5.999) ,TRUE,IF(AND(AND(AND($B$92>0.018,$B$92<0.027),$B$9 1>=6),$B$91<=6.999),TRUE,IF(AND(AND(AND($B$92>0.01 9,$B$92<0.0285),$B$91>=7),$B$91<=7.999),TRUE,IF(AN D(AND(AND($B$92>0.02,$B$92<0.03),$B$91>=8),$B$91<= 8.999),TRUE,IF(AND(AND(AND($B$92>0.021,$B$92<0.031 5),$B$91>=9),$B$91<=9.999),TRUE,IF(AND(AND(AND($B$ 92>0.022,$B$92<0.033),$B$91>=10),$B$91<=10.999),TR UE,IF(AND(AND(AND($B$92>0.023,$B$92<0.0345),$B$91> =11),$B$91<=11.999),TRUE,IF(AND(AND(AND($B$92>0.02 4,$B$92<0.036),$B$91>=12),$B$91<=12.999),TRUE,IF(A ND(AND(AND($B$92>0.025,$B$92<0.0375),$B$91>=13),$B $91<=13.999),TRUE,IF(AND(AND(AND($B$92>0.026,$B$92 <0.039),$B$91>=14),$B$91<=14.999),TRUE,IF(AND(AND( AND($B$92>0.027,$B$92<0.0405),$B$91>=15),$B$91<=15 .999),TRUE,IF(AND(AND(AND($B$92>0.028,$B$92<0.042) ,$B$91>=16),$B$91<=16.999),TRUE,IF(AND(AND(AND($B$ 92>0.029,$B$92<0.0435),$B$91>=17),$B$91<=17.999),T RUE,IF(AND(AND(AND($B$92>0.03,$B$92<0.045),$B$91>= 18),$B$91<=18.999),TRUE,IF(AND(AND(AND($B$92>0.031 ,$B$92<0.0465),$B$91>=19),$B$91<=19.999),TRUE,IF(A ND(AND(AND($B$92>0.032,$B$92<0.048),$B$91>=20),$B$ 91<=20.999),TRUE,IF(AND(AND(AND($B$92>0.033,$B$92< 0.0495),$B$91>=21),$B$91<=21.999),TRUE,IF(AND(AND( AND($B$92>0.034,$B$92<0.051),$B$91>=22),$B$91<=22. 999),TRUE,IF(AND(AND(AND($B$92>0.035,$B$92<0.0525) ,$B$91>=23),$B$91<=23.999),TRUE,IF(AND(AND(AND($B$ 92>0.036,$B$92<0.054),$B$91>=24),$B$91<=24.999),TR UE,IF(AND(AND(AND($B$92>0.037,$B$92<0.0555),$B$91> =25),$B$91<=25.999),TRUE)))))))))))))))))))))))))) ))
RED
=IF(AND(AND(OR($B$92<0.01,$B$92>0.015),$B$91>0),$B $91<2),TRUE,IF(AND(AND(OR($B$92<0.011,$B$92>0.0165 ),$B$91>=2),$B$91<=2.499),TRUE,IF(AND(AND(OR($B$92 <0.012,$B$92>0.018),$B$91>=2.5),$B$91<=2.999),TRUE ,IF(AND(AND(OR($B$92<0.013,$B$92>0.0195),$B$91>=3) ,$B$91<=3.499),TRUE,IF(AND(AND(OR($B$92<0.014,$B$9 2>0.021),$B$91>=3.5),$B$91<=3.999),TRUE,IF(AND(AND (OR($B$92<0.015,$B$92>0.0225),$B$91>=4),$B$91<=4.4 99),TRUE,IF(AND(AND(OR($B$92<0.016,$B$92>0.024),$B $91>=4.5),$B$91<=4.999),TRUE,IF(AND(AND(OR($B$92<0 .017,$B$92>0.0255),$B$91>=5),$B$91<=5.999),TRUE,IF (AND(AND(OR($B$92<0.018,$B$92>0.027),$B$91>=6),$B$ 91<=6.999),TRUE,IF(AND(AND(OR($B$92<0.019,$B$92>0. 0285),$B$91>=7),$B$91<=7.999),TRUE,IF(AND(AND(OR($ B$92<0.02,$B$92>0.03),$B$91>=8),$B$91<=8.999),TRUE ,IF(AND(AND(OR($B$92<0.021,$B$92>0.0315),$B$91>=9) ,$B$91<=9.999),TRUE,IF(AND(AND(OR($B$92<0.022,$B$9 2>0.033),$B$91>=10),$B$91<=10.999),TRUE,IF(AND(AND (OR($B$92<0.023,$B$92>0.0345),$B$91>=11),$B$91<=11 .999),TRUE,IF(AND(AND(OR($B$92<0.024,$B$92>0.036), $B$91>=12),$B$91<=12.999),TRUE,IF(AND(AND(OR($B$92 <0.025,$B$92>0.0375),$B$91>=13),$B$91<=13.999),TRU E,IF(AND(AND(OR($B$92<0.026,$B$92>0.039),$B$91>=14 ),$B$91<=14.999),TRUE,IF(AND(AND(OR($B$92<0.027,$B $92>0.0405),$B$91>=15),$B$91<=15.999),TRUE,IF(AND( AND(OR($B$92<0.028,$B$92>0.042),$B$91>=16),$B$91<= 16.999),TRUE,IF(AND(AND(OR($B$92<0.029,$B$92>0.043 5),$B$91>=17),$B$91<=17.999),TRUE,IF(AND(AND(OR($B $92<0.03,$B$92>0.045),$B$91>=18),$B$91<=18.999),TR UE,IF(AND(AND(OR($B$92<0.031,$B$92>0.0465),$B$91>= 19),$B$91<=19.999),TRUE,IF(AND(AND(OR($B$92<0.032, $B$92>0.048),$B$91>=20),$B$91<=20.999),TRUE,IF(AND (AND(OR($B$92<0.033,$B$92>0.0495),$B$91>=21),$B$91 <=21.999),TRUE,IF(AND(AND(OR($B$92<0.034,$B$92>0.0 51),$B$91>=22),$B$91<=22.999),TRUE,IF(AND(AND(OR($ B$92<0.035,$B$92>0.0525),$B$91>=23),$B$91<=23.999) ,TRUE,IF(AND(AND(OR($B$92<0.036,$B$92>0.054),$B$91 >=24),$B$91<=24.999),TRUE,IF(AND(AND(OR($B$92<0.03 7,$B$92>0.0555),$B$91>=25),$B$91<=25.999),TRUE)))) ))))))))))))))))))))))))
YELLOW
=IF(AND(AND(OR($B$92=0.01,$B$92=0.015),$B$91>0),$B $91<2),TRUE,IF(AND(AND(OR($B$92=0.011,$B$92=0.0165 ),$B$91>=2),$B$91<=2.499),TRUE,IF(AND(AND(OR($B$92 =0.012,$B$92=0.018),$B$91>=2.5),$B$91<=2.999),TRUE ,IF(AND(AND(OR($B$92=0.013,$B$92=0.0195),$B$91>=3) ,$B$91<=3.499),TRUE,IF(AND(AND(OR($B$92=0.014,$B$9 2=0.021),$B$91>=3.5),$B$91<=3.999),TRUE,IF(AND(AND (OR($B$92=0.015,$B$92=0.0225),$B$91>=4),$B$91<=4.4 99),TRUE,IF(AND(AND(OR($B$92=0.016,$B$92=0.024),$B $91>=4.5),$B$91<=4.999),TRUE,IF(AND(AND(OR($B$92=0 .017,$B$92=0.0255),$B$91>=5),$B$91<=5.999),TRUE,IF (AND(AND(OR($B$92=0.018,$B$92=0.027),$B$91>=6),$B$ 91<=6.999),TRUE,IF(AND(AND(OR($B$92=0.019,$B$92=0. 0285),$B$91>=7),$B$91<=7.999),TRUE,IF(AND(AND(OR($ B$92=0.02,$B$92=0.03),$B$91>=8),$B$91<=8.999),TRUE ,IF(AND(AND(OR($B$92=0.021,$B$92=0.0315),$B$91>=9) ,$B$91<=9.999),TRUE,IF(AND(AND(OR($B$92=0.022,$B$9 2=0.033),$B$91>=10),$B$91<=10.999),TRUE,IF(AND(AND (OR($B$92=0.023,$B$92=0.0345),$B$91>=11),$B$91<=11 .999),TRUE,IF(AND(AND(OR($B$92=0.024,$B$92=0.036), $B$91>=12),$B$91<=12.999),TRUE,IF(AND(AND(OR($B$92 =0.025,$B$92=0.0375),$B$91>=13),$B$91<=13.999),TRU E,IF(AND(AND(OR($B$92=0.026,$B$92=0.039),$B$91>=14 ),$B$91<=14.999),TRUE,IF(AND(AND(OR($B$92=0.027,$B $92=0.0405),$B$91>=15),$B$91<=15.999),TRUE,IF(AND( AND(OR($B$92=0.028,$B$92=0.042),$B$91>=16),$B$91<= 16.999),TRUE,IF(AND(AND(OR($B$92=0.029,$B$92=0.043 5),$B$91>=17),$B$91<=17.999),TRUE,IF(AND(AND(OR($B $92=0.03,$B$92=0.045),$B$91>=18),$B$91<=18.999),TR UE,IF(AND(AND(OR($B$92=0.031,$B$92=0.0465),$B$91>= 19),$B$91<=19.999),TRUE,IF(AND(AND(OR($B$92=0.032, $B$92=0.048),$B$91>=20),$B$91<=20.999),TRUE,IF(AND (AND(OR($B$92=0.033,$B$92=0.0495),$B$91>=21),$B$91 <=21.999),TRUE,IF(AND(AND(OR($B$92=0.034,$B$92=0.0 51),$B$91>=22),$B$91<=22.999),TRUE,IF(AND(AND(OR($ B$92=0.035,$B$92=0.0525),$B$91>=23),$B$91<=23.999) ,TRUE,IF(AND(AND(OR($B$92=0.036,$B$92=0.054),$B$91 >=24),$B$91<=24.999),TRUE,IF(AND(AND(OR($B$92=0.03 7,$B$92=0.0555),$B$91>=25),$B$91<=25.999),TRUE)))) ))))))))))))))))))))))))
|