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]

Is adding the NOT the only thing you changed/added to my formula?

I also removed the ISBLANK(I14) checks as they were redundant after checking if I14 wasn't blank.

 

[jtakw]

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!

If Fluff's formula works for you, then I believe all we need to do is change the AND to OR in my formula in Post #5 :

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

 

[Modify_inc]

I also removed the ISBLANK(I14) checks as they were redundant after checking if I14 wasn't blank.

Does using the NOT allow you to do If statements that would otherwise usually fail, or could've this formula been formulated to work without using a NOT? I'm really more curious than anything.

 

[Fluff]

You can do it without the NOT like jtakw has done in post#12

 

[Modify_inc]

If Fluff's formula works for you, then I believe all we need to do is change the AND to OR in my formula in Post #5 :

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

I had to step away for a few, but I'll definitely give it a shot when I get back. If this can be done without a Not then I'm wondering if I'm just overthinking it. I'm going to have to compare it to mine and see what I can learn.

Whether it works or not, I want to thank you again for your efforts and taking the time to help!

 

[Modify_inc]

If Fluff's formula works for you, then I believe all we need to do is change the AND to OR in my formula in Post #5 :

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

Ok, I just tested it, and it shows $80 for everything, even if D14 is blank (start time), which should make H14 equal $0 since you can't have an amount due if no time is entered, yet is equals $80. If I put a number in I14, it still shows $80 in H14, rather than $0.

I'll try to play around with it, maybe I can figure it out from this point.

 

[Modify_inc]

Ok, I just tested it, and it shows $80 for everything, even if D14 is blank (start time), which should make H14 equal $0 since you can't have an amount due if no time is entered, yet is equals $80. If I put a number in I14, it still shows $80 in H14, rather than $0.

I'll try to play around with it, maybe I can figure it out from this point.

Ok, actually it is working, but it is calculating the two rows up from the actual row. So H14 is actually calculating the values in row 12. That's weird, I put it in row 14, but the values changed to I12, D12, B12, F12, etc. Why would it do this, is there some offset being used?

Not sure if it was something I did, but I got it working. I just deleted it completely and stated over and it's working great!

Thanks again!

 

[jtakw]

Yeah, it sounded weird that it wasn't giving a 0 (zero) result when D14 is blank ( "" ), I was beginning to wonder if you had some kind of "hidden characters" and/or Space(s) in the D14.

Glad you got it sorted out.

And You're welcome.