Automatically Referencing Data Ranges In An Excel Scatter Plot

**groovymoon** · 12-06-2010, 09:55 AM

Hi,

I have a rather large data file. It has 11 columns and 3000 (sometimes more, sometimes less) rows of data. I would like to scatter plot a contiguous subset of this data. This subset is defined by an upper and lower boundary on "x" column of the data. Normally I hand pick the subset and then hand edit the series function in the plot. This can be quite time consuming and I want to automate the process using either a VBA macro or an excel formula or both. I have made some progress by using =address() and =match() to find the beginning and ending rows of the "x's" and "y's" I want to plot.

For a similar calculation issue "romperstomper" gave me the perfect answer using the following formula

=linest(index($A$18:$A$5007,startrow):index($A$18:$A$5007,endrow),index($B$18:$B$5007,startrow):inde x($B$18:$B$5007,endrow))
for example.

Where "startrow" and "endrow" refer to the two cells where the starting and ending cell address's of data I wish to perform some calculations on (and plot) are stored.

When I tried to insert the "indexing" part of this into the series formula for the plot I got an error... the errant formula looks like this:

'=SERIES('50kphR1'!$D$16,'50kphR1'!(index($A$18:$A9998,startrow))

index($118:$A$9998,endrow)),'50kphR1'!(index($D$18:$D$9998,startrow)):index(($D$18:D$9998,Dendrow)),1)

Note: The blue "frowny" face in the formula is the result of a ")" and a ":"

I am a total newbie to using excel's more advanced functions and really apreciate all the help this forum provides....

Thanks

**sweep** · 12-06-2010, 11:32 AM

Hi,

If you're using this:

=SERIES('50kphR1'!$D$16,'50kphR1'!(index(=SERIES('50kphR1'!$D$16,'50kphR1'!(index($A$18:$A9998,startrow)),startrow))............

startrow or the cell it refers to needs to be a number not an address, this way the series will begin at the nth row in the range $A$18:$A9998.

**groovymoon** · 12-06-2010, 03:11 PM

Thanks for the answer.. but either I do not understand the answer or I did not communicate my problem properly....

I have the determined, using =match(), the starting and ending rows of the data I want to plot, I just can't get that information properly into the =series() function for the plot.

The columns are fixed in the sheet, it's the rows that will change with every different data set.

**sweep** · 12-07-2010, 04:23 AM

You'll almost certainly find it easier to use named ranges to define your plotted data. Please take a look at the attachment to see if it helps..

**groovymoon** · 12-07-2010, 09:07 AM

Yes.... I used named ranges. Since I already new the "startrow" and "endrow" of the ranges I wished to plots I was able to define the address's of the named ranges using the =offset() function. The problem then became trivial... Thanks for all your help and thanks for the excellent example (much better than other I saw on the net).