Auto Updating Range in charts

**ddorron** · 08-01-2011, 03:32 PM

Hi, This is my first post here and hopefully im posting in the right area.

I am trying to make graphs from data that i gather daily, but i want the graphs only to display the last +/- 50 points, but when ever i do this excel automatically plots the points from 0 - til last point.

right now i am using dynamic name ranges

Please Login or Register  to view this content.

when ever i go above 22 in the previous code the range expands from 0, but if i go below 22 the plot shows the zoomed in version of what i want.

Overall, is it possible to automatically plot the last 50 points in the data set?

thanks,
daniel

**ChemistB** · 08-01-2011, 03:52 PM

Let's go with your current formula which is for the last 25 cells. Do you want to plot the last 25 cells or the last 25 values? Can you set up your data without all the spaces? That would make everything much easier, maybe using borders to separate sets of 3?

**ddorron** · 08-01-2011, 04:10 PM

the data sets that are recorded are 3 per day, so the space is used as a divider so you can see the differences between days in the graphs. i would like to plot ideally the last 50 values which is more or less the last 68 cells.

**ChemistB** · 08-01-2011, 05:04 PM

Try this formula. There's some variability based on # of blanks but this should work. You can replace 25 with a cell reference or a defined name constant to make it more versatile. Also, move "DZ" out to however far you think your series will go.

=OFFSET($E$3,0,COUNTA($E$3:$DZ$3)+COUNTA($E$3:$DZ$3)/3-25/3-25,1,25/3+25)
For 50 values, replace all the 25's with 50's.
Does that work for you?

**ddorron** · 08-02-2011, 08:37 AM

hi,first of all iwant to thank you for your help. The formula used does select the right number of values i want, but once again when this is graphed the range goes from 0- til the end. So basically the "zoom" to the most recent values is not working :/

**teylyn** · 08-04-2011, 08:44 PM

Change the chart type from XY Scatter to Line chart. Then the X axis will be interpreted as a category axis and will start with the label for the first data point.