Line Graph ranges in Excel
I want a line graph of monthly points starting at 123/31/2005 and extending to this month (and then next month, next month, next month and so on).
One data row is the stock exchange index divided by the index at 12/31/2005 times 100.
The other data row is my pension savings (similar to a US 401K plan) divided by the savings at 12/31/2005 times 100.
The horizontal axis is month ends (Dec 31, 2005; Jan 31, 2006; Feb 28, 2006 and so on). To make display easy I have a row that is manually entered as 'Dec 05, 'Jan 06, 'Feb 06 and so on.
The first try was 85 columns wide and 3 rows deep (as described above). The Line Graph is perfect and depicts what I wish to depict. I am not keen on having another 85 columns to add to the right.
The second try was to build 12 columns, each with 3 rows. One range for each year. The first range has 13 columns to account for 12/31/2005. When I build the Line Graph and select data range, I select the first row (say Stock Exchange points), hold down the control key and select the second range, the third range and so on to the 8th range which has 1 point (Jan 31, 2013 when I built the graph. This, too, works perfectly and provides the identical graph as the first try.
But now I have a problem.
I tried to add Feb 28, 2013 as a new data point. Any attempt to edit the range fails because I cannot get to the end or cursor in the graph range. Excel likes to interpret cursor movements as cell selections. Named ranges have the same limitation and for Named ranges, this is true in Office 2003, 2007 and now 2010.
So then I thought I would simply rebuild the range. Edit the data range, select the first 13 columns, control key, next 12, next 12 and so one.
However: I click on Jan 2013 (like I did a couple of weeks ago) and when I try to move the mouse to Feb., it beeps and the selection clears. I get an error on the 86th item in the 8th range. Clearly my selection is more complicated than Excel permits.
First question: Is my observation true? Does Excel have a limit in "multiple range selections" which is what I am doing? If no, why the error?
Second question: If I made a Named Range of the multiple ranges so as to graph the named range, are Named Ranges more flexible than graphs ranges?
Third question: If I made Name ranges of each year (8 so far), can I make a Line Graph of range 1+range 2+range 3 and so on? If this would work, I only have to edit the current year range (easy) and once complete, each year's range is fixed.
I am going to try the third approach as a test to see if it works, but I would appreciate some expert feedback on the three questions.
By the way, I did *not* sell out in the depths of 2008 and my broker has my pension savings doing better than the index over the 8 years. I still have a few years left before I must convert the savings into a pension plan. No question on this last point.
.... Thinkpads_User
One data row is the stock exchange index divided by the index at 12/31/2005 times 100.
The other data row is my pension savings (similar to a US 401K plan) divided by the savings at 12/31/2005 times 100.
The horizontal axis is month ends (Dec 31, 2005; Jan 31, 2006; Feb 28, 2006 and so on). To make display easy I have a row that is manually entered as 'Dec 05, 'Jan 06, 'Feb 06 and so on.
The first try was 85 columns wide and 3 rows deep (as described above). The Line Graph is perfect and depicts what I wish to depict. I am not keen on having another 85 columns to add to the right.
The second try was to build 12 columns, each with 3 rows. One range for each year. The first range has 13 columns to account for 12/31/2005. When I build the Line Graph and select data range, I select the first row (say Stock Exchange points), hold down the control key and select the second range, the third range and so on to the 8th range which has 1 point (Jan 31, 2013 when I built the graph. This, too, works perfectly and provides the identical graph as the first try.
But now I have a problem.
I tried to add Feb 28, 2013 as a new data point. Any attempt to edit the range fails because I cannot get to the end or cursor in the graph range. Excel likes to interpret cursor movements as cell selections. Named ranges have the same limitation and for Named ranges, this is true in Office 2003, 2007 and now 2010.
So then I thought I would simply rebuild the range. Edit the data range, select the first 13 columns, control key, next 12, next 12 and so one.
However: I click on Jan 2013 (like I did a couple of weeks ago) and when I try to move the mouse to Feb., it beeps and the selection clears. I get an error on the 86th item in the 8th range. Clearly my selection is more complicated than Excel permits.
First question: Is my observation true? Does Excel have a limit in "multiple range selections" which is what I am doing? If no, why the error?
Second question: If I made a Named Range of the multiple ranges so as to graph the named range, are Named Ranges more flexible than graphs ranges?
Third question: If I made Name ranges of each year (8 so far), can I make a Line Graph of range 1+range 2+range 3 and so on? If this would work, I only have to edit the current year range (easy) and once complete, each year's range is fixed.
I am going to try the third approach as a test to see if it works, but I would appreciate some expert feedback on the three questions.
By the way, I did *not* sell out in the depths of 2008 and my broker has my pension savings doing better than the index over the 8 years. I still have a few years left before I must convert the savings into a pension plan. No question on this last point.
.... Thinkpads_User
ASKER
@matthewspatrick: Give me an hour or so. I think I can fake the data. The stock exchange is public info, so I just need to fake a pension plan. No - I do not want to make it public.
... Thinkpads_User
... Thinkpads_User
ASKER
Here is the sample file. The stock exchange numbers are public. My own pension savings are private, so these numbers are fake (but each number is a fixed ratio of the original and private number). The file is an XLS file because I continue to keep my files in 97-2003 format because of the wide range of people I deal with.
... Thinkpads_User
Rsp-TSX-Comparison--Test-.xls
... Thinkpads_User
Rsp-TSX-Comparison--Test-.xls
ASKER
I tried the following (from Question 3 in the first post):
First, I deleted the Graph Sheet and I deleted the data collection sheet (no longer necessary at all). This leaves me with one main calculation sheet in the workbook and I called it DATA for ease of typing.
Then I made Named Ranges of the rows I need to graph. So I have 8 Named Ranges for the 8 years of Stock Exchange Data. This was straightforward to do. The Named Ranges are TSX_2006, TSX_2007, TSX_2008, TSX_2009 and so on.
Then I made a new worksheet and called it GRAPH (just like before).
Now I made a new line graph (simple) and went to select the data.
If I select =DATA!TSX_2006 and say OK to that, I get a one year line graph. That works as expected. So thus far, all is working as planned.
Problem: When I try to add the second range, I get a formula error.
=DATA!TSX_2006;DATA!TSX_20
Question: I cannot see how to concatenate Named Ranges in the Line Graph data selection. I think Named Ranges are the way to proceed because now I can edit the 8th named range as I add months to that 8th series.
How do I concatenate Named Ranges in the Graph Selection?
... Thinkpads_User
Rsp-TSX-Comparison--Test-two-.xls
First, I deleted the Graph Sheet and I deleted the data collection sheet (no longer necessary at all). This leaves me with one main calculation sheet in the workbook and I called it DATA for ease of typing.
Then I made Named Ranges of the rows I need to graph. So I have 8 Named Ranges for the 8 years of Stock Exchange Data. This was straightforward to do. The Named Ranges are TSX_2006, TSX_2007, TSX_2008, TSX_2009 and so on.
Then I made a new worksheet and called it GRAPH (just like before).
Now I made a new line graph (simple) and went to select the data.
If I select =DATA!TSX_2006 and say OK to that, I get a one year line graph. That works as expected. So thus far, all is working as planned.
Problem: When I try to add the second range, I get a formula error.
=DATA!TSX_2006;DATA!TSX_20
Question: I cannot see how to concatenate Named Ranges in the Line Graph data selection. I think Named Ranges are the way to proceed because now I can edit the 8th named range as I add months to that 8th series.
How do I concatenate Named Ranges in the Graph Selection?
... Thinkpads_User
Rsp-TSX-Comparison--Test-two-.xls
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
<aside>
It's been a long time since I have seen the acronym RRSP :)
Back about 11 years ago I did a project for a major Canadian bank in which they were examining ways to improve the systems they used to create and manage GICs for customers, and as part of that I got the high-level overview of the different types of savings accounts people were putting those GICs into, such as RRSP, RRIF, RESP, etc.
</aside>
It's been a long time since I have seen the acronym RRSP :)
Back about 11 years ago I did a project for a major Canadian bank in which they were examining ways to improve the systems they used to create and manage GICs for customers, and as part of that I got the high-level overview of the different types of savings accounts people were putting those GICs into, such as RRSP, RRIF, RESP, etc.
</aside>
ASKER
Thank you for all of that. I will need some time to set it up in a different way and test it.
On your second post:
RRSP is a Registered Retirement Savings Plan. Up to a limit, additions to your plan are tax deductible, and investment income accrues tax free. The whole lot is taxable on redemption, presumably at a lower tax rate when one retires.
RRIF is a Registered Retirement Income Fund. Not later than 71 years of age, you transfer the RRSP into the RRIF and then payments from the RRIF have minimum amounts that must be withdrawn each year (and taxed). The amounts (% of the total) increase each year. That factor maxes out at 20% of the plan value by age 94. You need equity-based investments to make all this work (hence the worksheet and graph in the first instance here) because GIC's just cannot sustain a pension plan.
RESP's are education savings instruments and not related to pensions.
.... Thinkpads_User
On your second post:
RRSP is a Registered Retirement Savings Plan. Up to a limit, additions to your plan are tax deductible, and investment income accrues tax free. The whole lot is taxable on redemption, presumably at a lower tax rate when one retires.
RRIF is a Registered Retirement Income Fund. Not later than 71 years of age, you transfer the RRSP into the RRIF and then payments from the RRIF have minimum amounts that must be withdrawn each year (and taxed). The amounts (% of the total) increase each year. That factor maxes out at 20% of the plan value by age 94. You need equity-based investments to make all this work (hence the worksheet and graph in the first instance here) because GIC's just cannot sustain a pension plan.
RESP's are education savings instruments and not related to pensions.
.... Thinkpads_User
ASKER
@matthewspatrick:
The tabular form did work quite well.
First attempt: I did this whole thing with a picture of the Stock Exchange and my own red pen to draw in my RRSP. This worked but (a) is not readily repeatable when I add points and (b) the differences were exaggerated because the Stock Exchange is around 12,000 but my RRSP's (in round thousands of dollars) is a very different scale. It looked like I had done better than I really had.
Second attempt: Get the Stock Exchange data from the Internet. I guess because of my source, the data was one Excel row with about 85 columns. I added my own data here and this worked fine, but I did not like the way it looked.
Third attempt: Series by year. I still like this because I can see a year at a time and this fits my way of thinking in numbers. But it was very hard to work with.
Fourth attempt: Data going down instead of across. This is your idea. It works fine and I will not live as long as the number of rows. So that is what I have done and the chart comes out fine.
The makeup of the Named Ranges is more complicated than I am used to. It appears the MATCH function is what allows the range to be dynamic. I will watch this going forward. I added the Feb 2013 point in, but that point will change data values for the next 18 days until Feb 28. I will see what happens when I add March.
Thanks for all your help.
.... Thinkpads_User
The tabular form did work quite well.
First attempt: I did this whole thing with a picture of the Stock Exchange and my own red pen to draw in my RRSP. This worked but (a) is not readily repeatable when I add points and (b) the differences were exaggerated because the Stock Exchange is around 12,000 but my RRSP's (in round thousands of dollars) is a very different scale. It looked like I had done better than I really had.
Second attempt: Get the Stock Exchange data from the Internet. I guess because of my source, the data was one Excel row with about 85 columns. I added my own data here and this worked fine, but I did not like the way it looked.
Third attempt: Series by year. I still like this because I can see a year at a time and this fits my way of thinking in numbers. But it was very hard to work with.
Fourth attempt: Data going down instead of across. This is your idea. It works fine and I will not live as long as the number of rows. So that is what I have done and the chart comes out fine.
The makeup of the Named Ranges is more complicated than I am used to. It appears the MATCH function is what allows the range to be dynamic. I will watch this going forward. I added the Feb 2013 point in, but that point will change data values for the next 18 days until Feb 28. I will see what happens when I add March.
Thanks for all your help.
.... Thinkpads_User
Glad to help :)
And yes, the MATCH expression is what makes it work. The key is to have it look for a numeric value that is guaranteed to be larger than anything in the column, which is why I used 10^99.
If I were looking for the last entry with text, I would have used:
MATCH(REPT("Z",100),Sheet1
And yes, the MATCH expression is what makes it work. The key is to have it look for a numeric value that is guaranteed to be larger than anything in the column, which is why I used 10^99.
If I were looking for the last entry with text, I would have used:
MATCH(REPT("Z",100),Sheet1
You can fake the data, unless your account balance is so high you want to show it off :)