combining conditional statements
 

I'd like to combine three conditional statements into one statement.

The three conditionals:

=IF(E20,H2-E2,"")

=IF(F20,"","")

=IF(AND(E2=0,F2=0),H2-D2,"")

Is this possible?

Thanks!

combining conditional statements
 

"wrosie" wrote:
I'd like to combine three conditional statements into one statement.
The three conditionals:
=IF(E20,H2-E2,"")
=IF(F20,"","")
=IF(AND(E2=0,F2=0),H2-D2,"")
Is this possible?

Certainly. But first, you need to specify exactly
what you want in English or in unambiguous logical
terms. The above is not 100% clear. My guess is
you mean: if E20 (regardless of F2), then H2-E2;
else (E2<=0, so ...) if E2=0 and F2=0, then H2-D2;
else (E2<=0 and F2<0), so ...) blank (""). That
can be written:

=IF(E20,H2-E2,IF(AND(E2=0,F2=0),H2-D2,""))

FYI, your second IF() expression (F20) is nonsense.
It says return blank ("") regardless of the value of F2.
I doubt that is what you intended, based on your third
IF() expression. That led me to my interpretation
above.

combining conditional statements
 

Thanks for your quick response.

For those with F20, the field with the results of the very complicated
formula should be blank. The spreadsheet tracks investments. Column F is
Sales. The column which contains the huge formula is Column I, Market Value
Change. If there's a value in Column F, it means that the stock was sold and
therefore there is no market value change to put in Column I. I hope that
makes sense...the whole thing makes my head spin.

Does that information change your suggestion for combining the statements?

I really appreciate the help!

(If I were doing this in Crystal Reports, I'd use If...Then...Else. I had no
idea how to do "else" in Excel.)

" wrote:

"wrosie" wrote:
I'd like to combine three conditional statements into one statement.
The three conditionals:
=IF(E20,H2-E2,"")
=IF(F20,"","")
=IF(AND(E2=0,F2=0),H2-D2,"")
Is this possible?

Certainly. But first, you need to specify exactly
what you want in English or in unambiguous logical
terms. The above is not 100% clear. My guess is
you mean: if E20 (regardless of F2), then H2-E2;
else (E2<=0, so ...) if E2=0 and F2=0, then H2-D2;
else (E2<=0 and F2<0), so ...) blank (""). That
can be written:

=IF(E20,H2-E2,IF(AND(E2=0,F2=0),H2-D2,""))

FYI, your second IF() expression (F20) is nonsense.
It says return blank ("") regardless of the value of F2.
I doubt that is what you intended, based on your third
IF() expression. That led me to my interpretation
above.

combining conditional statements
 

I should also have added this information:

I'm going to put the formula in Column I and fill down all the way. I need
the F0 returning nothing statement in there so that the I cells stay blank
for that condition. This spreadsheet needs to have formulas that can be
filled down as far as necessary without the user having to eliminate the
formula from some fields to deal with the F0 situation.

"wrosie" wrote:

Thanks for your quick response.

For those with F20, the field with the results of the very complicated
formula should be blank. The spreadsheet tracks investments. Column F is
Sales. The column which contains the huge formula is Column I, Market Value
Change. If there's a value in Column F, it means that the stock was sold and
therefore there is no market value change to put in Column I. I hope that
makes sense...the whole thing makes my head spin.

Does that information change your suggestion for combining the statements?

I really appreciate the help!

(If I were doing this in Crystal Reports, I'd use If...Then...Else. I had no
idea how to do "else" in Excel.)

" wrote:

"wrosie" wrote:
I'd like to combine three conditional statements into one statement.
The three conditionals:
=IF(E20,H2-E2,"")
=IF(F20,"","")
=IF(AND(E2=0,F2=0),H2-D2,"")
Is this possible?

Certainly. But first, you need to specify exactly
what you want in English or in unambiguous logical
terms. The above is not 100% clear. My guess is
you mean: if E20 (regardless of F2), then H2-E2;
else (E2<=0, so ...) if E2=0 and F2=0, then H2-D2;
else (E2<=0 and F2<0), so ...) blank (""). That
can be written:

=IF(E20,H2-E2,IF(AND(E2=0,F2=0),H2-D2,""))

FYI, your second IF() expression (F20) is nonsense.
It says return blank ("") regardless of the value of F2.
I doubt that is what you intended, based on your third
IF() expression. That led me to my interpretation
above.

combining conditional statements
 

"wrosie" wrote:
(If I were doing this in Crystal Reports, I'd use
If...Then...Else. I had no idea how to do "else" in Excel.)

Oh, come on! Anyone experienced with if-then-else
statements in any language should recognize "value_if_false"
to be the "else" part. See the Excel Help for the IF function.

For those with F20, the field with the results [...] should
be blank. [....] If there's a value in Column F, it means
that the stock was sold and therefore there is no market
value change to put in Column I.

Oh, that is a very different story. Combining with your
original posting, does the following pseudocode match
your intention for column I (I2, for example):

if F2 0, then display blank,
else if E2 0, then display H2-E2, else display H2-D2

One way to write that in Excel is:

=IF(F20, "", IF(E20, H2-E2, H2-D2))