# IF Statement Using AND and ISNULL

#### dcliffo

##### Board Regular
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?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Andrew Fergus

##### MrExcel MVP
Hi

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

Andrew

#### dcliffo

##### Board Regular
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

##### Well-known Member
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?

Replies
2
Views
733
Replies
9
Views
447
Replies
6
Views
186
Replies
1
Views
289
Replies
9
Views
912

1,181,056
Messages
5,927,865
Members
436,573
Latest member
CMR237

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back