Link to home
Start Free TrialLog in
Avatar of hedgeselect
hedgeselectFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Is this what you want?

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

Sid
Avatar of hedgeselect

ASKER

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.
Hi
Any chance at looking at this?  

Many thank.
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
But the purple line is still not reaching the latest date in the chart?
?
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.
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
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.
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
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.