IF Statements Not Working Correctly

[aread]

I want the value of a cell to be 0 if the corresponding cell in the Material column is not blank or if the corresponding cell in the Item Category column is not D or L, otherwise it should return a 1. All the other cells referenced are formatted as text even if there is a number in the Material column. The cells where the formula is at are formatted as a whole number.

I fixed the #NAME? error I had initially, but now I have tried the following formulas and I am getting all 0's in each, even if the outcome is false...

=IF(OR((NOT(ISBLANK([@Material]))), OR([@[Item Category]]="L",[@[Item Category]]="D")),0,1)
=IF(AND(ISBLANK([@Material]),OR([@[Item Category]]<>"D",[@[Item Category]]<>"L")),1,0)
=IF(OR(((ISBLANK([@Material]))), [@[Item Category]]="L",[@[Item Category]]="D"),0,IF(NOT(ISBLANK([@Material])),0,1))

There was another formula that returned all 1's but I forgot to copy it before changing it. Maybe I am just tired, but I can't seem to find the issue. Any ideas would be greatly appreciated!!

 

[Peter_SSs]

I want the value of a cell to be 0 if the corresponding cell in the Material column is not blank or if the corresponding cell in the Item Category column is not D or L, otherwise it should return a 1.

See if this does what you want. If not please give a small set of sample data of materials & Item Category and the expected result for each row.

=--AND([@Material]="",OR([@[Item Category]]={"D","L"}))

 

[Eric W]

Try:

=IF([@Material]<>"",0,IF(OR([@Item Category]={"D","L"}),1,0))

 

[DanteAmor]

Another way
=IF(OR([@Material]<>"",AND([@Item Category]<>{"D";"L"})),0,1)

 

[Little_Clubber]

Hi Aread, I hope I can help.



“I want the value of a cell to be 0 if the corresponding cell in the Material column is not blank”

So you want the cell to be 1 if the Material column is blank, let’s get that out of the way first:

Starting with =IF([@Material]<>””,1,…

So this will return a 1 and stop if the material column is not blank, if this condition is not met, then it will progress to the next function.

I want the value of a cell to be 0… If the corresponding cell in the Item Category column is not D or L

So again, if it is D or L, you want a 1.

So just focussing on the L, it would be:

=IF([@[Item Category]]="L",1,…

If L criteria is not met, then check for D:

=IF([@[Item Category]]="D",1,0)





Joining the three parts together, we should get:

=IF([@Material]<>””,1,IF([@[Item Category]]="L",1,IF([@[Item Category]]="D",1,0)



If you prefer to use an OR expression, then:



=IF(OR([@Material]<>””,[@[Item Category]]="L",[@[Item Category]]="D",1,0)



You’ve overcomplicated your formulae by focussing on what it is not.

Hope this works.

 

[aread]

Ok, I think I stated it wrong initially, so thank you for spelling it out for me again... Using parts of the above options, I came up with this and it works.

=IF(AND([@Material]="",OR([@[Item Category]]={"D";"L"})),0,1)

Thank you all for your help!!!

This is what I am now getting, which is correct:

Material	Free Text	Item Category
	0​	D
	0​	L
G46-000-00171	0​
G86-000-00066	0​
G38-000-00019	0​
	1​

 

[Peter_SSs]

=IF(AND([@Material]="",OR([@[Item Category]]={"D";"L"})),0,1)

So you could also shorten that to

=1-OR([@Material]&"|"&[@[Item Category]]={"|D","|L"})

Joining the three parts together, we should get:

=IF([@Material]<>””,1,IF([@[Item Category]]="L",1,IF([@[Item Category]]="D",1,0)



If you prefer to use an OR expression, then:



=IF(OR([@Material]<>””,[@[Item Category]]="L",[@[Item Category]]="D",1,0)

As they are written, neither of those are valid formulas as they have mis-matched number of ( and )