IF Statement Using AND and ISNULL

[dcliffo]

I have a complex formula that I need to use and IF statement with AND and ISNULL. I'm trying to calculate a division between 4 different columns. If one or both are null, then divide by the other columns, so am testing all possible scenarios. How do I do this? Say I have columns I and J and columns M and N. If both M and N are null, I want to divide I/J. If M is null, then I want to divide I/N. If N is null, then I want to divide J/M. I guess I'm strugglin with the other of how to place the IF with the ISNULL and the AND. Is it IF(AND ISNULL(N138, M138)? Maybe I just have too many parenthesis?

 

[Andrew Fergus]

Hi

How about something like this:

=IF(M1,J1/M1,IF(N1,I1/N1,I1/J1))

Andrew

 

[dcliffo]

It seems that once I post a problem, I tend to go ahead and figure it out myself. Here is what I came up with. However, if you have a simpler formula that would be great.

=IF(ISERROR(IF(ISBLANK(AND(M3,N3)),((H3/J3)*Q3)-P3,IF(ISBLANK(M3),((H3/N3)*Q3)-P3,IF(ISBLANK(N3),((M3/J3)*Q3)-P3,((M3/N3)*Q3)-P3)))),"",IF(ISBLANK(AND(M3,N3)),((H3/J3)*Q3)-P3,IF(ISBLANK(M3),((H3/N3)*Q3)-P3,IF(ISBLANK(N3),((M3/J3)*Q3)-P3,((M3/N3)*Q3)-P3))))

 

[mortgageman]

It seems that once I post a problem, I tend to go ahead and figure it out myself. Here is what I came up with. However, if you have a simpler formula that would be great.

=IF(ISERROR(IF(ISBLANK(AND(M3,N3)),((H3/J3)*Q3)-P3,IF(ISBLANK(M3),((H3/N3)*Q3)-P3,IF(ISBLANK(N3),((M3/J3)*Q3)-P3,((M3/N3)*Q3)-P3)))),"",IF(ISBLANK(AND(M3,N3)),((H3/J3)*Q3)-P3,IF(ISBLANK(M3),((H3/N3)*Q3)-P3,IF(ISBLANK(N3),((M3/J3)*Q3)-P3,((M3/N3)*Q3)-P3))))

Your post was almost 2 days after Andrew's - (which was a LOT simpler). Did you not see it? Or did it not work?