• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return last 5 entries of a dynamic list, ignoring blank cells

C

ChrisL

New Member
I'm trying to create a dashboard in which there is a chart that shows the "last 5" entries in a column, but only includes values from cells that are not empty. I'm trying to construct a worksheet formula (i.e. not using VBA). Would I use an array formula?


Example:


If the column contains 4;2;x;3;1;5;x;x;7;x;6;9;8 (assume that x means an empty cell), the last 5 entries would be 5;7;6;9;8.


I feel sure that I ought to be able to do this myself, but my brain is suffering from Monday overload :-| Many thanks in advance to all skilled Excel thinkers out there. And Happy Birthday to Hui :)
 
Can I see a sample workbook?


=INDIRECT("a"&MAX(ROW(A1:A20)*(A1:A20<>"")))

That will get you the last value. And you could probably get real cute with counts/offsets to have 5 cells return 5 values without having to use a helper table somewhere.


But I'm guessing that these blank cells have other data associated with them, which would indicate to me which would indicate to me that we can use dynamic ranges.
 
@dan_l

Very many thanks for your effort. The value list really is that simple - the values are in a column of fuel costs from a spreadsheet that records vehicle expenditure; where the item is not fuel, the cell contains a zero-length string, but could just as easily contain zero.


Ideally, I'd like to be able to have one solution, as the column grows downwards each time more fuel is purchased (i.e. not have to re-edit the formula each time). I tried using your suggestion to create helper cells - taking 1 off the ROW calculation, e.g. {=INDIRECT("e"&MAX(ROW($E$133:$E$145)-1*($E$133:$E$145<>"")))} for each of the 5 helper cells. Strangely, the values returned were 8,9,6,0,0. Nearly right!
 
I could do this with a helper column. I am sure there is a more elegant solution, but nothing comes to my mind quickly.


See this: http://chandoo.org/img/playground/last-5-values.xls


I have used INDEX, MATCH, COUNT formulas. Pretty straight-forward logic
 
@Chandoo


Many thanks for your help - you've given me food for thought.


Congratulations on an excellent Blog - chandoo.org is my first port of call for any Excel-related problem :)
 
Hi Chandoo, thanks for the idea.


I have made some changes...

1. the display is in reverse order. Since it is going to be a helper column i have cell e1 for input: the number of last figures to be displayed. and then the formula would be: INDEX($B$3:$B$101,MATCH(COUNT($B$3:$B$101)-$E$1+ROWS($D$3:D3),$C$3:$C$101,0))


2. Expending from point 1 if the choice does go beyond 5 or less than 5 I added iferror to read: IFERROR(INDEX($B$3:$B$101,MATCH( COUNT($B$3:$B$101)- $E$1 + ROWS( $D$3:D3) , $C$3:$C$101,0)),"")
 
Hello everyone, this post has been really useful before, but I am doing another project for which it doesnt seem to work.


I am trying to return the last 5 closed issues from an issue list.I am using a helper column to identify the closed issues. I have been experimenting with Chandoos example but instead of using numbers, change them for issues (text).


Any help you could offer would be grately appreciated.
 
Have a look at: http://chandoo.org/wp/2012/04/12/formula-forensics-018/
 
You must log in or register to reply here.
Back
Top