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

IF(IX,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, IV
2) H=U, IV
3) H=U, I<=V
4) H<U, I<=V
5) None of the above, so return '0'

Thank you for any help,
THEFALLGUY

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($I431X431,IF($I431=W431,IF($H431<W431,X431-W431,X431-$H431),0),IF($I431<=X431,IF($H431<X431,IF($H431=W 431,$I431-$H431,$I431-W431),0)))


"THEFALLGUY" wrote:

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

IF(IX,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<W, IX
2) H=W, IX
3) H=W, I<=X
4) H<W, I<=X
5) None of the above, so return '0'

Thank you for any help,
THEFALLGUY

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(IX,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


"THEFALLGUY" wrote:

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

IF(IX,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, IV
2) H=U, IV
3) H=U, I<=V
4) H<U, I<=V
5) None of the above, so return '0'

Thank you for any help,
THEFALLGUY

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

"Mark Hone" wrote:

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(IX,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


"THEFALLGUY" wrote:

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

IF(IX,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, IV
2) H=U, IV
3) H=U, I<=V
4) H<U, I<=V
5) None of the above, so return '0'

Thank you for any help,
THEFALLGUY

"THEFALLGUY" wrote in message
...
"THEFALLGUY" wrote:

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

IF(IX,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<W, IX
2) H=W, IX
3) H=W, I<=X
4) H<W, I<=X
5) None of the above, so return '0'

Thank you for any help,
THEFALLGUY

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($I431X431,IF($I431=W431,IF($H431<W431,X431-W431,X431-$H431),0),IF($I431<=X431,IF($H431<X431,IF($H431=W 431,$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

"THEFALLGUY" wrote in message
...

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

IF(IX,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, IV
2) H=U, IV
3) H=U, I<=V
4) H<U, I<=V
5) None of the above, so return '0'

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, IX
2) H=W, IX
3) H=W, I<=X
4) H<W, I<=X
5) None of the above, so return '0'"

Thank you for any help,
THEFALLGUY

"Mark Hone" wrote:

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(IX,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

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.
--
David Biddulph
Rowing web pages at http://www.biddulph.org.uk/
and http://ourworld.compuserve.com/homep...avid_biddulph/

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


"David Biddulph" wrote:

"THEFALLGUY" wrote in message
...

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

IF(IX,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, IV
2) H=U, IV
3) H=U, I<=V
4) H<U, I<=V
5) None of the above, so return '0'

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, IX
2) H=W, IX
3) H=W, I<=X
4) H<W, I<=X
5) None of the above, so return '0'"

Thank you for any help,
THEFALLGUY

"Mark Hone" wrote:

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(IX,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

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.
--
David Biddulph
Rowing web pages at http://www.biddulph.org.uk/
and http://ourworld.compuserve.com/homep...avid_biddulph/