A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Charting when number of rows are greater than 65536



 
 
Thread Tools Display Modes
  #1  
Old January 30th, 2006, 09:31 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Charting when number of rows are greater than 65536

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.

  #2  
Old January 31st, 2006, 03:49 AM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Charting when number of rows are greater than 65536

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/
_______

"Hari" wrote in message
oups.com...
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.



  #3  
Old January 31st, 2006, 06:30 AM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Charting when number of rows are greater than 65536

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?


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

  #4  
Old January 31st, 2006, 07:12 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Charting when number of rows are greater than 65536

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,Sh eet1!$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/
_______


"Hari" wrote in message
oups.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?


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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Microsoft Access Limits (file size, table records, users) Mike General Discussion 4 November 4th, 2005 04:01 AM
Excel should accomodate more number of rows than 65536, to load l. Laxman Charipally General Discussion 1 April 22nd, 2005 09:41 PM
Number of objects thomak General Discussion 3 February 17th, 2005 12:58 PM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM
Excel- Inserting a varying number of rows Ken Wright Worksheet Functions 1 March 20th, 2004 11:20 PM


All times are GMT +1. The time now is 06:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.