Tech: MS Excel 'IF' function - help requested

Subject: Tech: MS Excel 'IF' function - help requested
From: Ed.
Date: 27 Mar 03 - 04:45 PM

I've got a problem with microsoft excel.

I'm trying to set up a fairly complex 'IF' formula, but I'm obviously doing it wrong, because it doesn't work.

I don't want to clog up the music side of mudcat with an obscure technical query, so if anyone thinks that they may be able to help, I'd be very (nay, extemely) grateful for a PM, or an email to [e-mail] if you're not a member. I'll explain my problem in full that way.

Many thanks.

Ed

Subject: RE: Tech: MS Excel 'IF' function - help requested
From: NicoleC
Date: 27 Mar 03 - 05:46 PM

I'll PM you Ed, but be aware that you can only nest 7 levels of If statements in Excel, which is probably your problem. Vee have vays to vurk around dis dho!

Subject: RE: Tech: MS Excel 'IF' function - help requested
From: GUEST,Jon
Date: 28 Mar 03 - 08:28 AM

Just a couple of generalisations that can be helpful to anyone using Excel.

Using the Lookup functions may be more appropriate than nested if's.

If it really comes to the push, a VB module is an option. You can create your own custom function that way.

Subject: RE: Tech: MS Excel 'IF' function - help requested
From: Ed.
Date: 28 Mar 03 - 03:10 PM

Thanks Nicole for the PM and Jon for the advise.

'VLOOKUP' did the trick. Thanks a lot.

Ed

Subject: RE: Tech: MS Excel 'IF' function - help requested
From: Tiger
Date: 28 Mar 03 - 03:52 PM

A valuable tip is to do it stepwise, making sure it works each time. You could start with something like:

=IF(AND(1=1,1=1,1=1,1=1),"YES","NO")

make sure it works, then expand it to:

=IF(AND(B5>46,1=1,1=1,1=1),"YES","NO")

and so forth.

If you try to do it all at once, you'll never get there (but you know that already).

It can also help to create an intermediate result field encompassing half, or so, of the calculations - then a second operation performed on that intermediate field.

Keep it simple, do it stepwise, or debugging and checkout can be horrendous. If it's THAT complicated, how would you know the result was correct, anyway, without having something to hang your hat on?