Nested IF statement for drilling depth and type

[birdman_0711]

I am trying to have Excel calculate a drilling depth based on type of drilling chosen (ranges below) and to combine types if required. The first range (HTW) below is easily done with nested if's. However, the second range needs to build on the first (ie: below HQ/NTW would begin at 259) or calculate solo if the first range is null.

I have the first part of the build-up for HQ/NTW 201-400,however, when adding a scenario where drilling type 1 would be null I consistently get a "FALSE". I know it's complicated, but if anyone has any idea it would be appreciated. Here's what I have:

First part:
=IF(AND($F$12="HTW",F$13<400,F$13>200),400-F$13,IF(AND($F$12="HTW",$F$13>400),0,IF(AND($F$12="HTW",$F$13<400),0)))

Want to add null as follows to above formula (NOT WORKING):
IF((AND($F$12="",$F$14="HQ/NTW", F$15<400,F$15>200), F$15-200 IF(AND(F$15<400,F$15>200),F$15-200,IF(F$15>=400,200, IF(F$15<200,0))),0))))



[TABLE="width: 343"]
<tbody>[TR]
[TD]Drilling Type 1 :[/TD]
[TD]HTW[/TD]
[/TR]
[TR]
[TD]Drill Depth (m):[/TD]
[TD="align: right"]201[/TD]
[/TR]
[TR]
[TD]Drilling Type 2:[/TD]
[TD]HQ/NTW[/TD]
[/TR]
[TR]
[TD]Drill Depth (m):[/TD]
[TD="align: right"]259[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD]HTW: 0-200 m[/TD]
[/TR]
[TR]
[TD]HTW: 201-350 m[/TD]
[/TR]
[TR]
[TD]HTW: 351-500 m[/TD]
[/TR]
[TR]
[TD]HQ/NTW: 0-200 m[/TD]
[/TR]
[TR]
[TD]HQ/NTW: 201-400[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RonB1111]

1st formula returns FALSE when it gets to the last If condition and it is false - add something where noted in red below:
=IF(AND($F$12="HTW",F$13<400,F$13>200),400-F$13,IF(AND($F$12="HTW",$F$13>400),0,IF(AND($F$12="HTW",$F$13<400),0,"result when false")))

Your 2nd formula also has too many parenthesis at the end: maybe remove the 3 parenthesis highlighted in red:
IF((AND($F$12="",$F$14="HQ/NTW", F$15<400,F$15>200), F$15-200 IF(AND(F$15<400,F$15>200),F$15-200,IF(F$15>=400,200, IF(F$15<200,0))),0))))

 

[Surfboard]

Want to add null as follows to above formula (NOT WORKING):
IF((AND($F$12="",$F$14="HQ/NTW", F$15<400,F$15>200), F$15-200 IF(AND(F$15<400,F$15>200),F$15-200,IF(F$15>=400,200, IF(F$15<200,0))),0))))

Does this work:

=IF(AND($F$12="",$F$14="HQ/NTW",F$15<400,F$15>200),F$15-200,IF(AND(F$15<400,F$15>200),F$15-200,IF(F$15>=400,200,IF(F$15<200,0)))*0)