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

1. ## 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  Register To Reply

2. ## 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
>.
>  Register To Reply