UNSOLVED
Question About Setting Chart Limits & Memory Efficiency
Suppose I am using the following chart "Series" formula:
=Series(,SheetNm!$A$1:$A$300,SheetNm!$B$1:$B$300,1)
Because column "A" contains dates that are going to increase forever,
choosing row 300 is fine as long as the most recent date is stored in, say, row 50. In other words, I won't have to change the end limit for the chart from 300 to something larger for a long time.
My question has to do with the effects of changing the end limit to something really large, say, 5000, when I won't be using row 5000 for a long time from now. Will I use a lot more memory by choosing 5000 instead of 300, even though the data currently ends in row 50? What is the down side of choosing a large end limit when it is not currently needed?
=Series(,SheetNm!$A$1:$A$300,SheetNm!$B$1:$B$300,1)
Because column "A" contains dates that are going to increase forever,
choosing row 300 is fine as long as the most recent date is stored in, say, row 50. In other words, I won't have to change the end limit for the chart from 300 to something larger for a long time.
My question has to do with the effects of changing the end limit to something really large, say, 5000, when I won't be using row 5000 for a long time from now. Will I use a lot more memory by choosing 5000 instead of 300, even though the data currently ends in row 50? What is the down side of choosing a large end limit when it is not currently needed?
JRosenfeld
2 Intern
•
4383 Posts
337
0
Posted May 5th, 2004 20:00
You might also look at making a dynamic chart, for example
'Graph only the data that are currently available from a data set that will continue to grow'
http://www.tushar-mehta.com/index.html?http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/
There are other references and tutorials if you google for Excel dynamic chart
- Thanx for the reply. I guess it's just in the nature of a programmer to try to understand how someone else's program works. I just envision that a large maximum limit would maybe cause a lot of memory to be reserved to accommodate any values that might show up in that range. But what do I know? A really slick program could examine the slots following the last used slot and determine if there is any data or not that needs to be stored, then ignore all the emptys. But that's just wishful thinking. You see, I have conditions now and then in which neither of the suggested work arounds are appropriate. So for those charts I wind up having to change the upper limits about every two or three months. It's a nuisance, but I can do it. (Sigh)
abach
1728 Posts
337
0
Posted May 5th, 2004 12:00
Try the following:
Choose Insert, Name, Define
Enter ChartA Data as the name
In the Refers to field, type the following:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
Click OK
You could repeat this for Column B, naming it ChartB
To use this, refer to the Name in your formula. For example, =SUM(ChartA) will sum the non-empty cells in column A.
Though this does not give you the exact answer, I hope it points you in the right direction.