eliW
Well-known Member
- Joined
- Mar 2, 2002
- Messages
- 1,940
My purpose is to show a technique to control "dynamically" the number and interval of data points presented in charts, based on the answers to my question in: http://www.mrexcel.com/forum/showthread.php?t=295409
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
(Credits to all participants, and especially to PGC who was the first to introduce the formula, and TusharM who showed how to use it in the chart.)
<o></o>
<o></o>
Lets start by entering 100 numbers to A1:A100 and Naming the range: "_range1"
There is no problem to make a Line Chart presenting all 100 points of data.
<o></o>
<o></o>
But what happens if we want to present every second, third, or tenth point…etc.?
<o></o>
<o></o>
Ok. <o></o>
1) First of all lets put in any cell- a number, for example: <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:metricconverter ProductID="5 in" w:st="on">5 in</st1:metricconverter> ($D$1), to indicate that we want every fifth data point to be presented in the chart.
<o></o>
2) Now let us create another Named range “_range2” with the following formula. (It is vital to include path before any reference range in the formula), thus “_range2” is:
<o></o>
<o></o>
=N(OFFSET(Sheet1!$A$1,(ROW(INDIRECT("sheet1!1:"&INT(ROWS(Chart_Interval.xls!_range1)/Sheet1!$D$1)))-1)*Sheet1!$D$1,0,1,1))
<o></o>
<o></o>
3) Now create a simple line chart out of the 100 data points, and get the first Chart using _range1.
<o></o>
4) In order to control the number and interval of data points, right click on the chart and enter "Source Data"-"Series", there in "Values:" change the reference from =Chart_Interval.xls!_range1 to =Chart_Interval.xls!_range2
<o></o>
5) Now you can control the data points by changing the content of $D$1 as shown in the images.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>(Credits to all participants, and especially to PGC who was the first to introduce the formula, and TusharM who showed how to use it in the chart.)
<o
></o><o
></o>Lets start by entering 100 numbers to A1:A100 and Naming the range: "_range1"
There is no problem to make a Line Chart presenting all 100 points of data.
<o
></o><o
></o>But what happens if we want to present every second, third, or tenth point…etc.?
<o
></o><o
></o>Ok. <o
></o>1) First of all lets put in any cell- a number, for example: <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com
ffice:smarttags" /><st1:metricconverter ProductID="5 in" w:st="on">5 in</st1:metricconverter> ($D$1), to indicate that we want every fifth data point to be presented in the chart.<o
></o>2) Now let us create another Named range “_range2” with the following formula. (It is vital to include path before any reference range in the formula), thus “_range2” is:
<o
></o><o
></o>=N(OFFSET(Sheet1!$A$1,(ROW(INDIRECT("sheet1!1:"&INT(ROWS(Chart_Interval.xls!_range1)/Sheet1!$D$1)))-1)*Sheet1!$D$1,0,1,1))
<o
></o><o
></o>3) Now create a simple line chart out of the 100 data points, and get the first Chart using _range1.
<o
></o>4) In order to control the number and interval of data points, right click on the chart and enter "Source Data"-"Series", there in "Values:" change the reference from =Chart_Interval.xls!_range1 to =Chart_Interval.xls!_range2
<o
></o>5) Now you can control the data points by changing the content of $D$1 as shown in the images.
<o></o>
All the best.
></o>All the best.
<o></o>
Last edited:
