Closed Thread
Results 1 to 8 of 8

alternative to too many nested IF statements

  1. 09-08-2008, 12:35 PM #1
    lintcoop
    lintcoop is offline
    Registered User
    Join Date
    09-08-2008
    Location
    Maine
    Posts
    17

    alternative to too many nested IF statements

    I have a sheet with 10 columns. I need to start at the far right and work my way to the left to determine if a column has data (text) in it or not. If it does, I need to return the value, if not, I need it to continue to the left and check the next one.

    I have a statement that works but it only allows me to process 7 columns and I need to process all 10.

    =IF(ISTEXT(T5),(T5),IF(ISTEXT(R5),(R5),IF(ISTEXT(P5),(P5),IF(ISTEXT(N5),
    N5,IF(ISTEXT(L5),(L5),IF(ISTEXT(J5),(J5),IF(ISTEXT(H5),(H5),"error")))))))

    Any suggestions?

    I am using Excel 2007 but as far as I can tell, it seems the limitation of 7 nested statements still applies.


    ps. My first post. Hope I did a reasonable job.
    Last edited by lintcoop; 09-08-2008 at 12:38 PM. Reason: additional info.
    Reply With QuoteRegister To Reply
  2. 09-08-2008, 12:39 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    What's in the other columns, numbers?

    If so... try

    =IF(ISNA(LOOKUP(REPT("z",255),H5:T5)),"error",LOOKUP(REPT("z",255),H5:T5))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 09-08-2008, 12:56 PM #3
    lintcoop
    lintcoop is offline
    Registered User
    Join Date
    09-08-2008
    Location
    Maine
    Posts
    17
    Sweet! Your solution appears to solve my problem. I will attempt to determine what it is actually doing on my own.

    Thank you so much for the very quick response!!
    Reply With QuoteRegister To Reply
  4. 09-08-2008, 12:56 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    This doesn't depend on what's in the between columns:

    =INDEX( B5:T5, MATCH(2, IF(MOD(COLUMN(B5:T5), 2) = 0, IF(ISTEXT(B5:T5), 1) ), 1) )
    Reply With QuoteRegister To Reply
  5. 09-08-2008, 02:16 PM #5
    lintcoop
    lintcoop is offline
    Registered User
    Join Date
    09-08-2008
    Location
    Maine
    Posts
    17
    shg,

    Your option doesn't seem to work with my data... (returns a #N/A error) but I will look at it again to see if I can figure it out.

    In any case, thanks for the suggestion.
    Reply With QuoteRegister To Reply
  6. 07-06-2009, 04:19 PM #6
    airedale
    airedale is offline
    Registered User
    Join Date
    07-06-2009
    Location
    Greensboro, North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: alternative to too many nested IF statements

    I have a similar challenge though with 12 columns (cells ranging from AA80 to AL80) of numeric data. Beginning with the number in the rightmost column (cell AL80), check to see if the value is greater than 0. If greater than 0, enter the number in that cell. If 0, proceed to the next left column and see if that number is greater than 0...so forth until AA80. Would appreciate your help. AGM
    Reply With QuoteRegister To Reply
  7. 07-06-2009, 04:22 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: alternative to too many nested IF statements

    Welcome to the forum....

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Reply With QuoteRegister To Reply
  8. 07-06-2009, 04:23 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: alternative to too many nested IF statements

    Welcome to the forum, Airedale.

    Please take a few minutes to read the forum rules, and then start your own thread.

    Thanks.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
Closed Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VB and nested if statements
    By timmycl_7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-13-2008, 07:39 AM
  2. 15 nested if statements
    By john1985 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2007, 11:33 PM
  3. l7 limit on nested if and statements
    By censura in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-05-2007, 07:40 AM
  4. Nested conditional statements
    By Apel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2006, 01:28 PM
  5. Nested IF Statements Linking Problem
    By skratch in forum Excel General
    Replies: 2
    Last Post: 09-14-2006, 06:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1