IF Functions and tolerances.

  • Hello. i hope someone is able to help me but i am having some issues creating some IF formulas that need to return one of three results, the words, Green, Amber, or Red in the cell in column BA based on the criteria below.


    Input Green = If the value in Q5 is above the value in Cell L10 on the "Data" sheet

    Input Amber = If the value in Q5 is within 0.5 of the value in Cell L10 on the"Data" sheet

    Input Red = If the value in Q5 is 0.51 and below of the value in Cell L10 on the "Data" sheet


    The first two i have managed to do using the following formulas:


    =IF(Q5>'Data'!L10,"Green", "")

    =IF(ABS(Q5-'Data'!$L$10)<=0.5,"Amber", "")


    they seem to work fine based on my testing however, and i may be missing something stupidly obvious, when i try to do the formula for the third, 'Red' criteria it doesnt, work the formula i was using is:


    =IF(ABS(Q5-'Data'!$L$10)>=0.51,"Red","")


    i was probably naive thinking i could simply switch the comparator around and change the number


    Would someone be able to help with how the formula should be written? and if possible, help me nest them into one IF statement please.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!