What is the correct syntax for "does not equal" in Excel 10

Question

I have a formula, =IF(K57900<>"SALES",AND,IF(AJ57900="SIC","Review Plan Type"," "))

I'm trying to flag the data where the Job Function is not equal SALES, and the Bonus type is SIC, with the Text "Review Plan Type".    This formula is returning the flag where the Function is equal to Sales, instead of when Function does not equal sales. 

Thanks for any help

Answer 1

Kristin wrote:

I have a formula, =IF(K57900<>"SALES",AND,IF(AJ57900="SIC","Review Plan Type"," "))

I'm trying to flag the data where the Job Function is not equal SALES, and the Bonus type is SIC, with the Text "Review Plan Type".    This formula is returning the flag where the Function is equal to Sales, instead of when Function does not equal sales.  

Your syntax for "not equal" is correct (<>).  It is you syntax for AND that is wrong.  Write:

=IF(AND(K57900<>"SALES",AJ57900="SIC"),"Review Plan Type","")

Note:  I changed " " (with a space in between) to "" (the null string).  It is a good practice.  It might make other dependent formulas easier to write.

PS:  The posted syntax actually gives me a #NAME error, not "the flag" ("review...").  If that is what you meant, fine.  Otherwise, in the future, copy-and-paste from the Formula Bar, especially when you have a syntax question.

Answer 2

=IF(--(L2-D2)>0)*(L2-D2), "<",D2-L2)   

No, produces an error   I guess it is unbalance parentheses. 

=IF(--(L2-D2)>0)*(L2-D2)   Does not work either

Answer 3

=IF(--(L2-D2)>0)*(L2-D2), "<",D2-L2)   

No, produces an error   I guess it is unbalance parentheses. 

=IF(--(L2-D2)>0)*(L2-D2)   Does not work either 

what does the latter formula give you - an error or something else? I'm not sure what you're trying to accomplish with the "<" in the first formula. Can you give an example of what you're looking for?

Jim

Answer 4

The < inside of quotes is just printed as text.  It indicates that the first column is greater than the second column. , The idea is that if  L is smaller than D, then by how much. 

=IF(AND(D2<L2,L2<>0), "<",D2-L2)

The column is comparing search engine results ranking, where the lower number is better. 

This can be a comparison over weeks for one site, or between two websites for the same keywords. 

If column D is less than  L, then it is "beating" L in the rankings.  Basically I am trying to plot improvement in rankings for site 2, represented by the L column. 

The formula above works EXCEPT for the fact that it does not recognize Zero as a null result, and the NOT equals is not working <>  is supposed to. 

Jeff

Answer 5

The < inside of quotes is just printed as text.  It indicates that the first column is greater than the second column. , The idea is that if  L is smaller than D, then by how much. 

=IF(AND(D2<L2,L2<>0), "<",D2-L2)  

The column is comparing search engine results ranking, where the lower number is better. 

This can be a comparison over weeks for one site, or between two websites for the same keywords. 

If column D is less than  L, then it is "beating" L in the rankings.  Basically I am trying to plot improvement in rankings for site 2, represented by the L column. 

The formula above works EXCEPT for the fact that it does not recognize Zero as a null result, and the NOT equals is not working <>  is supposed to. 

Jeff

Hi Jeff,

maybe this is a semantics issue. If I copy your formula and test it on one of my worksheets, I get exactly what I'd expect: if d2 is less than l2, AND l2 does not equal zero, a less than sign appears. If either l2 is greater than d2, OR l2 is NOT equal to zero, then I get the result of d2-l2.

Zero isn't a null result, and the <> is working as it should, at least for me. What are you expecting to see as a result in the various scenarios?

Jim