Issue with nesting in 2010

[Jtzdragon77]

=IF(ISNUMBER(FIND("2002",P120)),"Above",IF(ISNUMBER(FIND("2003",P120)),"Above",IF(ISNUMBER(FIND("2004",P120)),"Above",IF(ISNUMBER(FIND("2005",P120)),"Above",IF(ISNUMBER(FIND("2005",P120)),"Above",IF(ISNUMBER(FIND("2006",P120)),"Above","Below"))))))

I am trying to create this formula that works but I need to look for all years from 2002-2012 in the text string. More specifically, I need it to return "Above" if excel finds any of 2002, 2003, 2004, etc.. all the way up to 2012. The above is the max that is being allowed... when I complete the formula to include all my needed search... excel tells me its too many nested arguments. What can I do to make this shorter?

[jeffreybrown]

If those are numbers you do not need the "".

Try

=IF(AND(P120>=2002,P120<=2012),"Above","Below")

[martindwilson]

assuming there is other text in the cell
=IF(ISNUMBER(--LOOKUP(2^15,SEARCH({"2002","2003","2004","2005","2006","2007","2008","2009","2010","2011","2012"},P120),{"2002","2003","2004","2005","2006","2007","2008","2009","2010","2011","2012"})),"above","below")

[FDibbins]

ok i know this is (kinda) off-topic, and im sure its been asked before (by me perhaps - i have seen it used often), but what the heck does the 2^15 do in that formula, and why/how do you use it?

[jeffreybrown]

It is a very large number.

2 to the 15th power = 2^15 = 32768. 32768 is one more character than is allowed in a cell. 32767 characters are allowed in a cell.

You could also use 9.99999999999999E+307 See this

Of course I could also be missing something

You could also you this as a function to get that same number

=POWER(2,15)

[Marcol]

Another wild guess without a sample of your data ...a

Please Login or Register  to view this content.

[Jtzdragon77]

I should have been more specific... there is text in the data. the cells comprise of years, makes, and models of vehicles and I am simply trying to seperate any cells that have any models 2002 and newer from those 2001 and older.

[jeffreybrown]

Yes, specifics are ofter important when trying to ascertain the method to answer your OP.

Care to post a sample workbook with what you have and what you expect?

[JosephP]

=IF(COUNT(FIND({2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012},P120)),"above","below")
entered normally or for a mite of future-proofing
=IF(COUNT(FIND(2001+ROW(INDIRECT("1:100")),P120)),"above","below" )
array-entered.

[martindwilson]

i think post #3 or #9 should do it ,if not what's wrong?

[Jtzdragon77]

# 3 did a great job... so thank you. One last question.. What If wanted to pull my search criteria, i.e the 2002, 2003, 2004, etc from another cell on another page?

[JosephP]

=IF(COUNT(FIND('some sheet'!A1:A10,P120)),"above","below")
array-entered oughta work.

[martindwilson]

a rethink gives
=IF(ISNUMBER(--LOOKUP(2^15,SEARCH(ROW($A$2002:$A$2099),'some sheet'!P120),ROW($A$2002:$A$2099))),"above","below")