• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Excel template needs fixing 3

One of the helpful guys on here programmed a spreadsheet for me.  Unfortunately on the 3rd worksheet, I cannot get the benchmark yellow and purple lines to correctly draw up to March 2010 (the same length of time as the fund cumulative graph).

The data for the benchmark indicies are in tab 2.  The dynamic cell referencing should automatically draw the chart based on the monthly data range for the fund (in worksheet 1).

Can someone please fix this for me such that the dynamic ranges still remain?

Thanks.

 dump-performance-performance-sta.xls
0
hedgeselect
Asked:
hedgeselect
  • 7
  • 3
  • 2
  • +1
1 Solution
 
SiddharthRoutCommented:
Is this what you want?

Sid
dump-performance-performance-sta.xls
0
 
SiddharthRoutCommented:
If yes, then we will have reset the named range formula I guess.

Sid
0
 
hedgeselectAuthor Commented:
Yes that is correct.  Excel the benchmark 1 (purple) also needs updating and it should automatically by showing for 2007 given that there is no data for it before then.

Thanks.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
hedgeselectAuthor Commented:
Hi
Any chance at looking at this?  

Many thank.
0
 
royhsiaoCommented:
Here is what I got.

1) right click on the line
2) click select data
3) click the benchmark that you want to update
4) click edit
5) check the series values and make sure it is as long as the horizontal Axis lables
6) click ok

If you want to remove the 0 on the chart you will need to use na() in the cell.

Please see attached files and pictures.
Chart.JPG
dump-performance-performance-sta.xls
0
 
hedgeselectAuthor Commented:
But the purple line is still not reaching the latest date in the chart?
0
 
hedgeselectAuthor Commented:
?
0
 
hedgeselectAuthor Commented:
FYI - The problem seems to occur when I overwrite the fund monthly returns in worksheet one, with more earlier data.  The benchmark indicies in worksheet 2, should then just update on the chart by starting whenever the earliest data of the benchmark is and not try to match the new fund data unless there is new earlier data for that too.
0
 
dlmilleCommented:
Sorry to have been late on this one.  I tied all BM indicies for full data to the actual FUND data.  The problem had to do with worksheet changes and you're wanting to align all this going forward.

1.  Same rows in Fund as in BM tabs - start on row 5 - important
2.  BM_1, BM_2, and BM_3 ranges now look at the FUND data with the same formula for dynamic range as FUND_1.  So if you have more/less FUND_1 data, the BM_X data will map accordingly.

Let me know if there are additional issues.

Cheers,

Dave
dump-performance-performance-sta.xls
0
 
hedgeselectAuthor Commented:
Thanks Dave.  As always you seem to know your stuff.

My premium membership to EE expires today, so will check your revision throughout the day and close the question by tonight.  I think you have sorted it though.  Will do some checks and revert.

Something I can note though - Benchmark 1 index, on the chart does not start at 1 (it is diagonal?).  It seem when you type that benchmark's performance for the first month, the cumulate return is not updated for that month is not updated?

Thx.
0
 
dlmilleCommented:
I'm not sure I follow.  The first datapoint for Benchmark 1 index is 0 until Jan 2007, then it IS 1.  Note the increment on the Y-Axis Label is 1.1, not 1.  Perhaps that's what you're keying in on.

Dave
0
 
dlmilleCommented:
See the formula change in the Benchmark Indicies Tab for BM1 - instead of looking up, I look at the monthly data to the left, then I can fill "empty" data with a NA() - which the chart doesn't plot.

This way, you can avoid having a zero value JUMP UP to a real value.

Make sense?

Dave
dump-performance-performance-sta.xls
0
 
hedgeselectAuthor Commented:
Thanks Dave, I'll accept your solution as my membership will now expire will probablt take out a new one soon.  Will come back to you if there any further issues.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now