IF isnull with and

This is a snippet from a nested if statement: IF IsNull([5]) AND IsNull([6]) AND [1] = [2] = [3] = [4] then 1 else 2 endif results in a 2 when I'd expect a 1.

If I can get this to work, the full statement should, which just accounts for nulls in up to 5 columns.

Perhaps there's an easier way. I want to know if all the cells that aren't Null contain the same data or value (in the example, it would be columns 1-4.)

Not all rows have data in all columns, but the Nulls are always to the right.

If And.PNG

Formula SS.PNG

Help

Expression

Accepted answers

The syntax as...

 [1]=[2] AND [2]=[3] AND [3]=[4]

works how you would like @Number4

Try

IF IsNull([5])

AND IsNull([6])

AND [1] = [2]

AND [2] = [3]

AND [3] = [4]

then 1 else 2 endif

All comments

The syntax as...

 [1]=[2] AND [2]=[3] AND [3]=[4]

works how you would like @Number4

Try

IF IsNull([5])

AND IsNull([6])

AND [1] = [2]

AND [2] = [3]

AND [3] = [4]

then 1 else 2 endif

Okay, so this fixes it:

IF Isnull([5]) AND isnull([6]) AND [1] = [2] AND [1] = [3] AND [1] = [4] then 1 else 2 endif

Is there a way to make [1] = [2] =[3] = [4] work instead?

Thanks @BenMoss and @rarmstrong. You both came up with what I figured out as well, hadn't seen your posts before posting mine.

Is there a way to group them instead?

Quick Links

Tags

This months top contributors

AkimasaKajitani 387

Hollingsworth 335