Nested IF-statements

L

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
 
P

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
 
B

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)
 
B

Bob Phillips

touché

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top