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.0165),$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),TRUE,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(AND(AND(AND($B$92>0.015,$B$92<0.0225),$B$91>=4),$B$91<=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$91>=6),$B$91<=6.999),TRUE,IF(AND(AND(AND($B$92>0.019,$B$92<0.0285),$B$91>=7),$B$91<=7.999),TRUE,IF(AND(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.0315),$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),TRUE,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.024,$B$92<0.036),$B$91>=12),$B$91<=12.999),TRUE,IF(AND(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),TRUE,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(AND(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),TRUE,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$92>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.499),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$92>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),TRUE,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.0435),$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),TRUE,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.051),$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.037,$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$92=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.499),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$92=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),TRUE,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.0435),$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),TRUE,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.051),$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.037,$B$92=0.0555),$B$91>=25),$B$91<=25.999),TRUE))))))))))))))))))))))))))))
(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.0165),$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),TRUE,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(AND(AND(AND($B$92>0.015,$B$92<0.0225),$B$91>=4),$B$91<=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$91>=6),$B$91<=6.999),TRUE,IF(AND(AND(AND($B$92>0.019,$B$92<0.0285),$B$91>=7),$B$91<=7.999),TRUE,IF(AND(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.0315),$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),TRUE,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.024,$B$92<0.036),$B$91>=12),$B$91<=12.999),TRUE,IF(AND(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),TRUE,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(AND(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),TRUE,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$92>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.499),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$92>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),TRUE,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.0435),$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),TRUE,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.051),$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.037,$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$92=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.499),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$92=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),TRUE,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.0435),$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),TRUE,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.051),$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.037,$B$92=0.0555),$B$91>=25),$B$91<=25.999),TRUE))))))))))))))))))))))))))))
