Data Validation Using Nested IF Formula Problem?

Question

I'm using Excel 2003 and am trying to use this formula for data validation of cell B4:

=IF(B4<2,FALSE,IF(ISEVEN(B4),IF(B4<B3/B5,FALSE,TRUE),IF(B3/B5>B4-1,FALSE,TRUE)))

Does data validation not like nested IF functions?  It gives me the "you cannot refer to other worksheets or workbook" error when I try to plug in this formula.

Also, would this work with Excel 2007?

I feel like I'm missing something obvious here, so any help would be appreciated.

Answer 1

Thanks a bunch for the simplified formulas!

However, it's still not working for me (Excel 2003 version 11.8328.8329).  Once I plug the formula into the data validation dialogue (under "Custom"), it still gives me the error.

Interestingly, once I remove ISEVEN or ISODD from the formula, the dialogue does not produce an error message.

I'll try it in Excel 2007 when I have the chance tomorrow.

The ISODD/ISEVEN functions are part of the Analysis ToolPak add-in in excel versions 2003 and earlier. Since the ATP is a separate file that's why you get the error.

These functions were added to the Excel application in Excel 2007 so you should not get the error in that version (and later).

If you need something that is backward compatible (will work in any version of Excel) then you can use something like this.

For ISODD:

MOD(cell_ref,2)>0

For ISEVEN:

MOD(cell_ref,2)=0

--

Biff

Microsoft Excel MVP

Answer 2

I don't see why it wouldn't work. It does work in Excel 2007.

By the way, the formula can be rewritten to avoid nested IFs:

=IF(B4<2,FALSE,B3/B5<=B4-ISODD(B4))

or even

=AND(B4>=2,B3/B5<=B4-ISODD(B4))

Answer 3

Biff:  I already had the Analysis Toolpaks installed.  The formula worked fine in the spreadsheet as I tested it there before posting in this thread, but not in the data validation dialogue.

That said, your "backward compatible" substitutions work marvelously. :)

If you have the ATP installed those functions should work ON THE WORKSHEET. It's when you try to use them in things like conditional formatting and data validation where Excel will complain.

--

Biff

Microsoft Excel MVP