Help with Nested If Statements

[Guest]

I have a scenario that has 5 scenarios, and I need to nest them into one
formula within a single cell:

IF(I>X,IF(H<W,X-W,X-H))
IF(I<=X,IF(H>=W,I-H,I-W))
IF NEITHER OF THE ABOVE STATEMENTS IS TRUE, THEN RETURN '0'

Here are the five scenarios broken down:
1) H<U, I>V
2) H>=U, I>V
3) H>=U, I<=V
4) H<U, I<=V
5) None of the above, so return '0'

Thank you for any help,
THEFALLGUY

 

[Guest]

All 'U's should be 'W's and all 'V's should be 'X's

I was able to come up with a formula that returns the correct value for the
first four scenarios, but if the condition does not apply to any of the first
four scenarios, then instead of returning '0', Excel returns #############
within the cell. I cannot figure our how to get rid of the pound signs.

My formula:
=IF($I431>X431,IF($I431>=W431,IF($H431<W431,X431-W431,X431-$H431),0),IF($I431<=X431,IF($H431<X431,IF($H431>=W431,$I431-$H431,$I431-W431),0)))

 

[Guest]

Hi TheFallGuy,

Given that your two IF statements cover all possibilities with respect to
the relationship between I and X, you could combine them into one statement
as follows:

=IF(I>X,IF(H<W,X-W,X-H),IF(H>=W,I-H,I-W))

This is basically inserting the second IF statement into the 'value if
false' parameter of the first IF statement.

As far as I can tell from your logic, it is impossible for both statements
to be false so a value of zero need not be returned.

Hope this helps,

Mark

 

[Guest]

Mark,
Thank you for your input. However, there are scenarios where the values do
not fall within the two If Statements, and thus should be '0'. The formula
in my second post is producing the correct solution, but it does not produce
clean data for the cells that should have a '0' value.

David

 

[David Biddulph]

All 'U's should be 'W's and all 'V's should be 'X's

I was able to come up with a formula that returns the correct value for
the
first four scenarios, but if the condition does not apply to any of the
first
four scenarios, then instead of returning '0', Excel returns #############
within the cell. I cannot figure our how to get rid of the pound signs.

My formula:
=IF($I431>X431,IF($I431>=W431,IF($H431<W431,X431-W431,X431-$H431),0),IF($I431<=X431,IF($H431<X431,IF($H431>=W431,$I431-$H431,$I431-W431),0)))

So where do the tests:
IF($I431>=W431 and IF($H431<X431
come from?

These weren't in your original criteria.

Your criteria compared I with X (but not with W), and compared H with W (but
not with X).

 

[David Biddulph]

and later said:
"All 'U's should be 'W's and all 'V's should be 'X's"

Hence:
"> Here are the five scenarios broken down:

1) H<W, I>X
2) H>=W, I>X
3) H>=W, I<=X
4) H<W, I<=X
5) None of the above, so return '0'"

Mark,
Thank you for your input. However, there are scenarios where the values
do
not fall within the two If Statements, and thus should be '0'. The
formula
in my second post is producing the correct solution, but it does not
produce
clean data for the cells that should have a '0' value.

David

How can you get a case where it doesn't fit your first two IF statements?

Is I > X ?
Is H < W ?

Each of the above questions has two possible answers. Combining these,
there are 4 possible answers. There isn't a 5th option.

 

[Guest]

I agree that the 4 cases cover all possibilities; I also prefer min and max
to if statements of this form - so if (h<w,x-w,x-h) should be x-max(h,w).
Doing this the formula simplifies to something like (forgive mistakes)
=max(x,i)-max(h,w)