Using IF AND

[Nanaia]

I'm trying to create a warning formula to tell me if C2 is within 2.25 of the value of A2 divided by 2. I think it will be an IF AND combination but could be wrong. To explain further here is an example; if A2 is 110 and C2 is any number equal to or between 52.75 and 57.25; basically if C2 equals (A2/2)+ or - 2.25 then I want this formula to flag with the word ALERT or something similar. The value in A2 and C2 will vary but the + or - 2.25 will never vary.
Thank you.

 

[Joyner]

Hello, If I understand, I think this:

=IF(OR(C2>(A2/2)+2.25,C2<(A2/2)-2.25),"ALERT","")[/COLOR][/SIZE][/FONT]

 

[Nanaia]

That's an excellent start! Thanks!

Next spin on the same formula is whether there is an easy (relatively speaking) formula to add to the above if C2 is always in feet & inches (i.e. 2'-11 5/8") an A2 is always a decimal. The above formula gives an alert every time C2 has any value in it, even if its value is outside the +-2.25.

 

[Special-K99]

2'-11 5/8" is a text value due to the quote/quotes.
You cant perform mathematical operations on text that contains quotes, it will need to be converted

Solution to original problem

=IF(ABS(C2-A2/2)<=2.25<"ALERT","")

 

[Nanaia]

I'm unable to get the ABS to work. When tested with a number that should cause an alert, it doesn't "alert".

I added a conversion column to resolve the FT-IN to inches only. The ALERT still does not work though. When I use Joyner's formula it alerts for every C2 entry regardless of the value, when I use Special-K99's ABS formula it doesn't alert to any value in C2, even if the value should cause an alert.

 

[Special-K99]

Sorry typo

<
should be ,
thus

=IF(ABS(C2-A2/2)<=2.25,"ALERT","")

 

[Nanaia]

Thank you. I thought I caught it but had simply removed the < instead of replacing it with a comma.
It appears to be working now. Thank you again for your help!