+ Reply to Thread
Results 1 to 7 of 7

RE-write if function with isna function for more values

  1. 07-10-2012, 04:41 AM #1
    funtastic
    funtastic is offline
    Registered User
    Join Date
    10-01-2011
    Location
    baynon
    MS-Off Ver
    Excel 2003
    Posts
    61

    RE-write if function with isna function for more values

    how can i rewrite this function



    Please Login or Register  to view this content.


    i have a list of values in the column A1:A20 i want to use the




    the functions is :
    if the value of A1 is wrong use A2, but if the value of A2 is wrong use A3, and if the value of A3 is wrong use A4 ...... and so on up to A20
    Reply With QuoteRegister To Reply
  2. 07-10-2012, 04:43 AM #2
    Bob Phillips
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: RE-write if function with isna function for more values

    Try this array formula

    =INDEX(A1:A4,MIN(IF(NOT(ISNA(A1:A4)),ROW(A1:A4))))
    Reply With QuoteRegister To Reply
  3. 07-10-2012, 06:21 AM #3
    funtastic
    funtastic is offline
    Registered User
    Join Date
    10-01-2011
    Location
    baynon
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: RE-write if function with isna function for more values

    Dear Bob

    no not success with me



    this is my list
    it should give me value 5


    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    5
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    #VALUE!
    Reply With QuoteRegister To Reply
  4. 07-10-2012, 06:29 AM #4
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: RE-write if function with isna function for more values

    In that case, replace Bob's formula with

    =INDEX(A1:A20,MIN(IF(NOT(ISERROR(A1:A20)),ROW(A1:A20))))

    Please confirm with Ctrl+Shift+Enter and not just Enter as this is an array formula
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  5. 07-10-2012, 06:49 AM #5
    funtastic
    funtastic is offline
    Registered User
    Join Date
    10-01-2011
    Location
    baynon
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: RE-write if function with isna function for more values

    Yessssssssssss success
    Reply With QuoteRegister To Reply
  6. 07-10-2012, 07:03 AM #6
    Bob Phillips
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: RE-write if function with isna function for more values

    Quote Originally Posted by funtastic View Post
    Dear Bob

    no not success with me
    I'll bet you didn't array-enter it.
    Reply With QuoteRegister To Reply
  7. 07-12-2012, 04:33 AM #7
    funtastic
    funtastic is offline
    Registered User
    Join Date
    10-01-2011
    Location
    baynon
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: RE-write if function with isna function for more values

    im a very very basc user . u can say underground excel user
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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