+ Reply to Thread
Results 1 to 8 of 8

Return last numeric value in a row (greater than zero; ignore errors)

  1. #1
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Return last numeric value in a row (greater than zero; ignore errors)

    I have a row which contains numbers, blank cells and error values.

    I need a formula which will return:
    1. The last numeric value, ignoring blank cells and error values.
    2. And that value has to be greater than 1.

    I all my searches, I could only find formulas that return fulfill one of the two conditions. But never one that could fulfill both simultaneously.

    Thanks in advance.
    Last edited by Luis Ah-Hoy Jr.; 05-31-2017 at 01:33 AM.
    Luis A. D. Ah-Hoy

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,946

    Re: Return last numeric value in a row (greater than zero; ignore errors)

    Array-enter (enter using Ctrl-Shift-Enter) this formula, acting on row 2:

    =INDEX(2:2,MAX(IF(ISNUMBER(2:2),IF(2:2>0,COLUMN(2:2)))))
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Return last numeric value in a row (greater than zero; ignore errors)

    Hi,

    See the attached file.

    Try the following array formula:

    =INDEX(A4:S4,,MAX(IF((A4:S4>1)*ISNUMBER(A4:S4),COLUMN(A4:S4))))

    to be confirmed by pressing CTRL+SHIFT+ENTER
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Return last numeric value in a row (greater than zero; ignore errors)

    First of all, thanks to both of you for trying. Unfortunately your formulas are not quite there yet, and I'll explain below.

    BERNIE:
    Your formula seems to (always) return the first number in the range. Be it greater or smaller than 1.

    CBATRODY:
    Your formula seemed to work. But if any of the error value(s) contained in the row are a result of a formula (instead of a string)... the return is always the corresponding error.
    Last edited by Luis Ah-Hoy Jr.; 05-30-2017 at 06:30 PM.

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,946

    Re: Return last numeric value in a row (greater than zero; ignore errors)

    You need to enter the formula using Ctrl-Shift-Enter, not just Enter.
    Capture.JPG
    Last edited by Bernie Deitrick; 05-30-2017 at 07:03 PM.

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,910

    Re: Return last numeric value in a row (greater than zero; ignore errors)

    One way:

    =LOOKUP(10^307,A4:S4/(A4:S4>1))

  7. #7
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Return last numeric value in a row (greater than zero; ignore errors)

    PHUOCAM:
    Brilliant!
    Your formula worked flawlessly. Even when I expanded the range and the type of cell contents.
    Thank you very much!


    EDIT:
    --------

    BERNIE:
    I stand corrected. Using Ctrl+Shift+Enter makes your formula work as well.
    Thank you.
    I'm baffled by it though. Really would like to understand the mechanics of it.
    Last edited by Luis Ah-Hoy Jr.; 05-31-2017 at 01:41 AM.

  8. #8
    Registered User
    Join Date
    07-02-2018
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    1

    Re: Return last numeric value in a row (greater than zero; ignore errors)

    This worked great for me, Thank You!

    Quote Originally Posted by Phuocam View Post
    One way:

    =LOOKUP(10^307,A4:S4/(A4:S4>1))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to ignore errors in a sum
    By Jaspal in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-09-2013, 04:37 AM
  2. Replies: 14
    Last Post: 06-14-2013, 09:00 PM
  3. [SOLVED] How to do sum-up and ignore errors?
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-26-2013, 07:03 AM
  4. Ignore Errors in SUM
    By w424637 in forum Excel General
    Replies: 3
    Last Post: 02-13-2007, 02:42 AM
  5. Find Numeric Criterion in Column & Return the Numeric Value from Row above
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-27-2006, 09:55 AM
  6. [SOLVED] Match Single Numeric Criteria and Return Multiple Numeric Labels
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2005, 04:10 PM
  7. [SOLVED] Match Single Numeric Criteria and Return Multiple Numeric Labels
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-29-2005, 04:50 PM
  8. [SOLVED] vlookup, but ignore errors if #n/a
    By Jess in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1