# Nested IF statement with multiple conditions

#### Modify_inc

##### New Member
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:

### 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
=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
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
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

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
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

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
Have you tried my suggestion in post#2?

#### Modify_inc

##### New Member
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
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!

Replies
0
Views
97
Replies
8
Views
43
Replies
6
Views
102
Replies
3
Views
68
Replies
0
Views
21