Nested IF-statements

[Lars]

Hi group,

Sinve years I use nested IF-statements in Excel. But I have always had
this suspicion that I go about building them in the wrong way.
Generally I use the function wizard.

First of all I want to take out all zeros that would indicate an empty
cell, before I get into any serious stuff. Doing that I kind of do it
backwards, including a lot of copying and pasting, using notepad as a
scratchbook, eventually arriving at something like this

=IF(AL7=1;"";IF(AA7>0;IF(B7="L";AA7;IF(B7="S";AA7;""));""))

I wish the wizard for IF-statements could be expanded with more boxes
for those cases when I have not yet arrived to the end of the
statement. Like when building logical statements.

If you were to copy my statement into Excel and then click on the
function "button" you would see that all of the below has been built
elsewhere, and pasted into the lower box.
IF(AA7>0;IF(B7="L";AA7;IF(B7="S";AA7;""));"")

Is there a better way?

Excel 2003.


Lars
Stockholm

 

[Pete_UK]

I'm not sure what you are asking, but you can re-write your formula to
get rid of one of the IFs as:

=IF(AA7>0;IF(OR(B7="L";B7="S");AA7;"");"")

or

=IF(AA7="";"";IF(OR(B7="L";B7="S");AA7;""))

if you are just checking for a blank cell.

I'm not sure if this constitutes a "better way".

Hope this helps.

Pete

 

[Bob Phillips]

Lars,

I am not really sure what the question is, but a few thoughts

=IF(AL7=1;"";IF(AA7>0;IF(B7="L";AA7;IF(B7="S";AA7;""));""))

can be simplified as

=IF(AND(AL7<>1;AA7>0;OR(B7="L";B7="S"));AA7;"")

Similarly

=IF(AA7>0;IF(B7="L";AA7;IF(B7="S";AA7;""));"")

can be simplified as

=IF(AND(AA7>0;OR(B7="L";B7="S"));AA7;"")

If you want to remove all zeros, just filter the column,
Data>Filter>Autofilter..., then click the dropdown, and select custom with a
value of not equal 0, and copy the visible data to another sheet.

Any help?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

touché

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)