 # 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.

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

• Thank you! no idea how i didn't figure out that if it's not 1 or 2 then it has to be 3, thank you for your help

## Participate now!

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