Formula for rightmost set of values

[brumanchu]

Hello,
I am setting up a spreadsheet to return the last 6 values of a running data
set.

I can get the rightmost value with the lookup function, but if I want to
constantly return only the last six values to trend on a chart, how can I
accomplish that?

Thanks for the help,
bruce

 

[Mike H]

Hi,

To return the last 6 values put this in a cell and array enter with
CTRL+Shift+Enter and then drag 5 columns to the right. You can then chart the
5 extracted numbers.

=OFFSET(INDEX(2:2,,COUNT($2:$2)),,ROW(A1)-COLUMN(A1),1,100)

Mike

 

[Fred Smith]

How can I accomplish that?

Give us more information. What does your lookup function look like? Where
are the 6 values that you want returned?

Regards,
Fred

 

[brumanchu]

The worksheet is set up as follows:
column e, row 1 = date (input)
column e, row 2 = # people used (input)
column e, row 3 = # hours worked
column e, row 4 = total hours (row 3 * row 2)

Columns f through CY will be populated as data is inputted on an ongoing
basis.
I want to trend only the last 6 reported values for total hours on a chart.

The lookup function is =LOOKUP(1E+100,E4:CY4)

Hope this helps,
Bruce