Excel If function not functioning if reference a named range

[bisel]

I apologize if this has been asked before, but I cannot find a relevant post.

I have a simple IF function where I compare a value of a cell to a named range (also a single cell). Like this ...

=IF(AM$268 > display_years,TRUE,FALSE) ... display_years is the named range that is a single cell.
​

In the case above, the named range, "display_years" = 15 and the value of cell AM$268 = 2047, but the result of the logical test is FALSE.

If I remove all references to a named range, the logical test will work fine. For example,

=IF(AM$268 > $AQ$264,TRUE,FALSE) ... where $AQ$264 = 15, the result of the logical test is TRUE
​

Can some one explain why this happening?

Thanks,

Steve

 

[footoo]

Are you sure that "display_years" refers to AQ264 ?

 

[bisel]

No it doesn't. Display_years refers to another cell on a different sheet

steve

 

[mikerickson]

Is it possible that AM268 and/or display_years are string values?

What do =ISNUMBER(AM268) and =ISNUMBER(display_years) return?

What is the definition of display_years? If it a reference to a cell with a formula, what is that formula?

 

[bisel]

I have found a work around and it may be the definitive answer. The cell that the named range was referring to in the IF statement (function) is part of a range of cells that are referenced in another, unrelated function, that is a LOOKUP function. If I create the IF statement using a named range that is not part of the range referenced by the LOOKUP function, it works just fine. Ultimately, that is what I have done to resolve this problem. It seems odd that this works, but one of many odd things that I have found that occur in Excel.

 

[Aladin Akyurek]

I have found a work around and it may be the definitive answer. The cell that the named range was referring to in the IF statement (function) is part of a range of cells that are referenced in another, unrelated function, that is a LOOKUP function. If I create the IF statement using a named range that is not part of the range referenced by the LOOKUP function, it works just fine. Ultimately, that is what I have done to resolve this problem. It seems odd that this works, but one of many odd things that I have found that occur in Excel.

"Odd things" should be exposed, if they are just that, "odd things".

What does the following do?

What do you get with the following?

=COUNT(display_years)

=COUNTA(display_years)