Nested IF statement with multiple conditions

[Modify_inc]

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

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

 

[Fluff]

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]

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

 

[Modify_inc]

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]

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]

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]

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

 

[Fluff]

Have you tried my suggestion in post#2?

 

[Modify_inc]

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]

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!