Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ANJ
 
Posts: n/a
Default 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   Report Post  
Jason Morin
 
Posts: n/a
Default

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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
making used values fall from a list static69 Excel Discussion (Misc queries) 4 February 17th 05 03:04 AM
Blank cells? David Excel Worksheet Functions 7 February 3rd 05 08:49 AM
Extracting Values on one list and not another B Schwarz Excel Discussion (Misc queries) 4 January 7th 05 02:48 PM
how to skip the blank cells nayeemoddin Excel Discussion (Misc queries) 1 December 6th 04 08:07 AM
copy blank cells Vicneswari Murugan Excel Discussion (Misc queries) 0 December 1st 04 04:33 AM


All times are GMT +1. The time now is 12:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"