+ Reply to Thread
Results 1 to 2 of 2

Function to list values of last 3 non-blank cells in a vertical bl

  1. #1
    ANJ
    Guest

    Function to list values of last 3 non-blank cells in a vertical bl

    Excel 2003. Looking for a function to examine a specified vertical block of
    cells (ie- B1:B25) and output the values of the last three cells that are not
    blank. For example, if cells B1:B7 contain text, this function should return
    the values of B7, B6 and B5. If I add text to cell B8, the function should
    then return the values of B8, B7 and B6.

    Any hints or knowledge of functions that do similar things would be
    appreciated.

    TIA

  2. #2
    Jason Morin
    Guest

    Re: Function to list values of last 3 non-blank cells in a vertical bl

    If there are no blanks *between* values, then copy this
    into row 1 of an open column and fill down to row 3:

    =OFFSET($B$1,COUNTA($B$1:$B$25)-ROW(),)

    Otherwise, use:

    =INDEX($B$1:$B$25,LARGE(IF($B$1:$B$25<>"",ROW($1:$25)),ROW
    ()))

    But with this formula, enter it into a cell into row,
    press ctrl + shift + enter, then fill down.

    HTH
    Jason
    Atlanta, GA

    >-----Original Message-----
    >Excel 2003. Looking for a function to examine a

    specified vertical block of
    >cells (ie- B1:B25) and output the values of the last

    three cells that are not
    >blank. For example, if cells B1:B7 contain text, this

    function should return
    >the values of B7, B6 and B5. If I add text to cell B8,

    the function should
    >then return the values of B8, B7 and B6.
    >
    >Any hints or knowledge of functions that do similar

    things would be
    >appreciated.
    >
    >TIA
    >.
    >


+ 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