+ Reply to Thread
Results 1 to 3 of 3

Function to return the latest non-zero value in a range of cells .

  1. #1
    hungryman
    Guest

    Function to return the latest non-zero value in a range of cells .

    I have a list of dates witten sequentially in a row on the sheet. Under some
    of these I have some values. I need to find the non-blank value associated
    with the latest date. Is there an easy way of doing this?

  2. #2
    Jason Morin
    Guest

    Re: Function to return the latest non-zero value in a range of cells .

    Try:

    =INDEX(B1:B21,MAX(IF(ISNUMBER(B1:B21)*(B1:B21<>0),ROW
    (B1:B21))))

    Array-entered (meaning press ctrl/shift/enter). Change
    the ranges to suit.

    This formula will pull the last value, skipping over
    blanks, text values, and zeroes.

    HTH
    Jason
    Atlanta, GA

    >-----Original Message-----
    >I have a list of dates witten sequentially in a row on

    the sheet. Under some
    >of these I have some values. I need to find the non-

    blank value associated
    >with the latest date. Is there an easy way of doing

    this?
    >.
    >


  3. #3
    JulieD
    Guest

    Re: Function to return the latest non-zero value in a range of cells .

    Hi

    to find the last non-blank value
    =INDEX(A2:G2,MATCH(9.99999999999999E+307,A2:G2))

    to find the last non-zero value

    =LOOKUP(2,1/(-A2:G2<>0),A2:G2)

    Hope this helps

    Cheers

    JulieD

    "hungryman" <hungryman@discussions.microsoft.com> wrote in message
    news:0094882E-3EE7-4AAA-98A7-6BF729151F1F@microsoft.com...

    >I have a list of dates witten sequentially in a row on the sheet. Under
    >some
    > of these I have some values. I need to find the non-blank value
    > associated
    > with the latest date. Is there an easy way of doing this?




+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1