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)))) ))))))))))))))))))))))))

Attached Files

Final Wearing Formula.docx (14.8 KB, 11 views)

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
Present post should not be answered as long as OP has not added links. Thanks

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post