IF Functions and tolerances.

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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:


    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.

  • Assuming Red is the value you want to display if the condition for Green & Amber are not met, this might be all you need:

    =IF(Q5>Data!L10,"Green",IF(ABS(Q5-Data!$L$10)<=0.5,"Amber", "Red"))

Participate now!

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