List last 5 non-zero entries

R

Rayo K

I am trying to setup a graph that will show the last five values from a range
in Excel.

The range is setup so that there could be gaps in the list:

Week 1 20000
Week 2
Week 3 19000
Week 4 21000
..... .....

I setup a table to put the last 5 values in but I need to know how to
exclude blanks
 
J

JMB

If the data is in A1:A15 (change range to suit), try

=INDEX($A$1:$A$15,LARGE(($A$1:$A$15<>"")*(ROW($A$1:$A$15)-ROW($A$1)+1),5))

Change the 5 at the end to 4,3,2,1 to get the rest of the numbers you need.
It is an array formula and needs to be entered w/Cntrl+Shift+Enter.
 
T

Teethless mama

Let's say your data in A1:A100

In B1:
=INDEX($A$1:$A$100,LARGE(IF(($A$1:$A$100<>"")*(ROW($A$1:$A$100)),ROW(INDIRECT("1:"&ROWS($A$1:$A$100)))),ROWS($1:1)))

ctrl+shift+enter, not just enter
Drag the Fill Handle to copy from B1 to B5
 
J

JMB

I notice you said non-zero, not non-blank. I think <>0 will eliminate cells
that are either 0 or empty (but not cells that contain an empty string - "").

=INDEX($A$1:$A$15,LARGE(($A$1:$A$15<>0)*(ROW($A$1:$A$15)-ROW($A$1)+1),5))

or to eliminate 0, empty cells, and empty strings, this appears to work:

=INDEX($A$1:$A$15,LARGE(($A$1:$A$15<>0)*($A$1:$A$15<>"")*(ROW($A$1:$A$15)-ROW($A$1)+1),5))
 
Top