Nested IF statement with multiple conditions

Modify_inc

New Member
Joined
Feb 26, 2009
Messages
48
I'm trying to write a nested If statement and it's driving me nuts.

Basically, if I14 is blank then check for these multiple conditions, which will require more If statements. If I14 is NOT blank then check for these multiple conditions, which of course involve more If statements. I can't seem to figure out how to write this or formulate it correctly.

Here is what I have that works, as it shows $0 in H14, but I need H14 to also equal $0 if I14 has an amount or value in it:

If it helps, this formula is in the H14 cell

Code:
=IF(AND(ISBLANK(I14),ISBLANK(D14)),0,IF(ISBLANK(I14),IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2))))

So basically, none of these conditions here matter if I14 is NOT blank or has a value in it. Just make H14 = $0:
IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2))))
 
Last edited:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
How about
=IF(NOT(ISBLANK(I14)),"0",IF(ISBLANK(D14),0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2))))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

So between your current formula And what you're saying, it doesn't matter whether I14 is Blank or Not, as long as D14 is Blank, you want H14 to show 0 (zero)?
And, you also say "none of these conditions here matter if I14 is NOT blank or has a value in it. Just make H14 = $0:...……."

So why the need to test I14 at all?

=IF(ISBLANK(D14),0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2)))

Edit:

If in case D14 contains a formula Blank "", rather than being Truly Blank (Empty):

=IF(D14="",0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2)))
 
Last edited:

Modify_inc

New Member
Joined
Feb 26, 2009
Messages
48
Hi,

So between your current formula And what you're saying, it doesn't matter whether I14 is Blank or Not, as long as D14 is Blank, you want H14 to show 0 (zero)?
And, you also say "none of these conditions here matter if I14 is NOT blank or has a value in it. Just make H14 = $0:...……."

So why the need to test I14 at all?

=IF(ISBLANK(D14),0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2)))

Edit:

If in case D14 contains a formula Blank "", rather than being Truly Blank (Empty):

=IF(D14="",0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2)))

Hi there and thanks for the reply and suggestion!

Basically, if I14 has a value in it, then it trumps everything else, because it is the final amount. None of the other conditions matter as they now become null, which is why I need H14 to show or equal 0.

Hope that helps

Thanks again!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

Ok..

May be this is what you mean?
If I14 has Any value, AND D14 is Blank, then result 0 (zero), otherwise do the rest of the formula?

=IF(AND(I14<>"",D14=""),0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2)))
 

Modify_inc

New Member
Joined
Feb 26, 2009
Messages
48
Ok..

May be this is what you mean?
If I14 has Any value, AND D14 is Blank, then result 0 (zero), otherwise do the rest of the formula?

=IF(AND(I14<>"",D14=""),0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2)))

Thanks again for the suggestion, I will give it a shot.

Also, I meant to add to my previous reply to you, but it said I was past the 10 minute mark for editing. Here is what I wanted to add:

Yes, it does matter if I14 is blank or not. If I14 is blank, then H14 needs to execute all those conditions in my original formula to generate the total amount due. If it is not blank, then I have manually added the final total myself in I14, therefore H14 needs to equal 0, since now I14 trumps any conditions in H14.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

Ok, if the above formula is Not what you need, then please also explain how D14 plays in the logic of the formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
Have you tried my suggestion in post#2?
 

Modify_inc

New Member
Joined
Feb 26, 2009
Messages
48
Ok..

May be this is what you mean?
If I14 has Any value, AND D14 is Blank, then result 0 (zero), otherwise do the rest of the formula?

=IF(AND(I14<>"",D14=""),0,IF(B14="Remote",$I$2*F14,IF(B14="Local",$G$2*F14+20,$H$2*F14+$H$2)))

Ok, I tried it, but it doesn't calculate my previous conditions correctly, as it now shows 0 for everything.

If it helps, I will add a few more details:
D14 is the start time, E14 is the end time, F14 is the total hours calculated from D14 and E14. H14 provides the formula to calculate the rate per hour owed based on the type of work performed: Remote, Local or Other. Sometimes I don't go by this calculated total, instead, I enter a custom amount in I14
 

Modify_inc

New Member
Joined
Feb 26, 2009
Messages
48
Have you tried my suggestion in post#2?

Just did, and it works great! I had to make a small change. I had to remove the quotes around the zero so it would be treated as a number rather than a string.

Is adding the NOT the only thing you changed/added to my formula? I'm going to have to study it some more and find out exactly how and why it's working.

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,836
Members
409,839
Latest member
akashsadhu
Top