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

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.


"Rayo K" wrote:

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

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


"Rayo K" wrote:

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

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$1 5<"")*(ROW($A$1:$A$15)-ROW($A$1)+1),5))



"JMB" wrote:

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.


"Rayo K" wrote:

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