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
hedgeselectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.