Charting when number of rows are greater than 65536

  • Thread starter Thread starter Hari
  • Start date Start date
H

Hari

Hi,

I have Residual output for Regression from SAS in CSV format.

I have to make a scatter plot between Residual and the Predicted
variable. Problem is that my original data set in SAS has close to 0.2
million rows, so I have 0.2 million rows of residual information.

I can open this data in Textpad and paste to excel 65536 rows at a
time.

So, in first copy-paste my dependent variable and residual would be
lets say in Columns E and F. In next copy-paste I can paste these 2
columns in K and L and subsequently in Q, R and then fianlly in W and
X.

I am aware of named functions in excel which can be used within charts
etc. what I want o know is whether I can create a named formula which
will be union of the ranges given above. (Am aware of the union
function within VBA, but do we have equivalent Non-VBA formula based
solution for this?).

Please guide me.

regards,
Hari
India

PS : Please note am not using excel for data management and the above
requirement is just for one-time "appraisal" of a model.
 
Hari -

A chart series in Excel is limited to 32,000 points, and total points in a
chart is limited to 256,000. There's no sense in trying to combine ranges
for charting when each of these ranges is already more than twice the
allowable number of points in a series.

Does it make sense to decimate the data, that is, plot every Nth point? Or
plot some kind of average function?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,

As always thanks for your post. I went through some of the archives
after I posted this ("post-haste") and see that one can simulate the
look by plotting more than one series and giving all of them the same
look. Thanks for tip regarding total points being 256,000, I hadnt come
across that.
Does it make sense to decimate the data, that is, plot every Nth point? Or
plot some kind of average function?
Click to expand...

I think its "probably" more standard to plot residual using all
points(am not knowledgeale enough on this). To be frank am also not
aware as to whether the client would be comfortable with plotting every
Nth point.

I have one more query, though I wont need it for my present use. In
excel is there a formula which can join 2 ranges and make them one
continuous range. Especially in case of charting when all the points
may not be contiguous (lets say we have 4 columns of data which are not
together and 10 rows and each of the 20 points in the first 1 and 3rd
column may have to be plotted against the 2nd and the 4th column. One
technque (which I probably came across somewhere) is to use probably a
linking formula in some other 2 columns where these 2 columns for each
axis are stacked one below the other.Are there more elegant solutions.

regards,
Hari
India
 
Right, residuals make no sense to show every Nth point. But still, a chart
with tens or hundreds of thousands of points takes a long time to plot, and
you'll end up with a mass of black marks.

You can incorporate multiple areas into a range. Often it's as easy as
selecting the first area, then holding Shift while selecting additional
areas. You'll get something that looks like this:

=SUM(F3:F10,H6:H14,J16:K25)

When done with a chart's source data range, the multiple pieces of the range
are each prefixed with the sheet name, and they are all surrounded by
parentheses:

=SERIES(,,(Sheet1!$E$9:$E$18,Sheet1!$F$22:$F$29,Sheet1!$G$15:$G$20,Sheet1!$H$20:$H$27),1)

This formula does not have a Name or X Values argument, only Y Values and
Plot Order.

In a chart, your total length of the formula is around 1024 characters, and
the way Excel is structured, you can only use about 250 characters per
argument. Given the multiple appearance of the sheet name, you can see how
you will quickly use up the allotted characters by CTRL-Clicking. This
limitation is one good reason to use a second data region for charting,
which uses formulas to link to the data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top