IF Statement Using AND and ISNULL

dcliffo

Board Regular
Joined
Jun 18, 2003
Messages
148
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? :oops:
 

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
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hi

How about something like this:

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

Andrew
 

dcliffo

Board Regular
Joined
Jun 18, 2003
Messages
148
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
Joined
Jun 30, 2005
Messages
2,015
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?
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top