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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Microsoft Access Limits (file size, table records, users) | Mike | General Discussion | 4 | November 4th, 2005 03: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 11:58 AM |
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 10:20 PM |