Charting only the last 6 months of data entered once a month

[bobdino24]

Hello,

I am trying to chart just the last 6 months of data on a graph that updates as you enter in the next months data.

The data being charted is just one number (tested monthly power of a laser) and is in a column. Once I figure out how to make thiis work I need to make 6 seperate graphs which I can do easily. The data however is mixed in a table but has a date tested.

There are also 6 different laser tests that can be done, so when the user enters in the data from a test, they select a power test from a drop down window. I have already made a table seperating the "power setting 1" test from the others but I cant figure out how to make the chart only plot the last 6 points, and I dont know how to make the horizontal axis line up with the correct month instead of just listing numbers.

I know its only my first post, but I am new to excel and have been doing alot of reading in forums and cant figure the rest of this one out on my own.

Here is the excel sheet I have been working on (attached)

[zbor]

Does this help:

=IF(AND($D4="Power Setting 1",L13>=(TODAY()-182)),G4,NA())

[bobdino24]

I was messing around with the TODAY( ) function too but wasnt really making progress. Im sure its the right way to go though did you make a new column with that and use select data to change to the new column? and did you find a way to change the horizontal axis? When I chart the new column it is the same as before but i think i am missing something

[bobdino24]

just kidding I figured out how to change the horizontal axis to the date thanks. Now I have a graph that has the right dates on the bottom, but do you know how to make it update to only chart the most recent 6 entrys?

Thanks,

Bob

[pimpmychart]

Hi Bob,

you can use the function OFFSET to do that for you.
Have a look at the attachment. Change the value for 'last data point' in cell B3 and this one plus the 11 previous periods will be displayed in the chart.
Let me know if you have further questions.

Regards

Holger

OFFSET.xlsx