Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
wrosie
 
Posts: n/a
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
wrosie
 
Posts: n/a
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
wrosie
 
Posts: n/a
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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))

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting IF statements, Conditional Formatting Nick Danger Excel Discussion (Misc queries) 2 September 26th 05 10:31 PM
Conditional IF Statements - cumulative calc. Shams Excel Worksheet Functions 1 August 9th 05 07:25 PM
Conditional Formating multible if statements Roy Excel Discussion (Misc queries) 4 July 22nd 05 09:34 PM
iF STATEMENTS WITHIN CONDITIONAL FORMATS E Halliday Excel Worksheet Functions 4 February 23rd 05 02:05 AM
Combining IF and multiple SUMIF statements, if A>0 & B is between Lee Excel Worksheet Functions 1 January 3rd 05 07:46 PM


All times are GMT +1. The time now is 05:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"