Solved

Help with my excel spreadsheet

Posted on 2011-02-27
13
328 Views
Last Modified: 2012-05-11
Could someone please help me with the attached spreadsheet.  I am trying to create a template for future use.  Details as follows.

1st worksheet: Contains monthly fund performance information and cumulative value of $1 growth based on MoM %.  The date range goes back a few years so that I can copy and paste future funds in there which might have more performance history.

2nd worksheet: Contains 3 benchmark monthly performance information (data matches the same date range as fund

3rd worksheet: Contains a chart of the fund performance + table of statistics on the Month on Month return

What I need:

1. Why is cell C22 and C26 on worksheet 3 showing a green error comment?
2. I'm not sure how to automatically put in the cumulative calander quarterly and annual return in columns C and D respectively on worksheet 1.  Could someone please do this for me.

The quarterly return should only show every 3 months at the end of each calander quarter.  The yearly return should only show the 12-months cumulative return once per year in Dec (and of course will only take into account the data available for that year).  The annualised return should show a monthly % in each cell for each month and represent the sum of all monthly data returns (for that year only) divided by the number of months of data for that year so far, multiplied by 12.  When it comes to Dec, if the formula is correct, it should match the yearly return column.

3. Why, when I click on one of the series on the chart in worksheet 3, does it then not VISUALLY reference (and flash) the appropiate data range in worksheet 1 - it only shows the formula?

4. For the summary statistics table on worksheet 3, could someone please dynamically update the annual returns section including the YTD 2011 keeping in mind that when I use this template for future, the YTD 2011 will need to reflect more months.

Thanks. Master-Statistics-template.xls
0
Comment
Question by:hedgeselect
  • 8
  • 5
13 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

these are really four questions rolled into one. You'll get better response from experts if you split them up, so formula experts can tackle the formula bits and charting people can do the charting Q.

1. The green error is explained when you tick the yellow ! sign that pops up when selecting the cell. You'll find it's not an error, but a warning "Formula omits adjacent cells". You can turn off these warnings in Tools > Options > Error checking

3. Since your data is not on the same chart, you will not see the data selected. If you want to check what data is used in the chart, you can right-click the series, then select "Source Data" from the context menu. In the dialog, go to the Series tab and click in the Values box for the series. This will activate the source sheet and you will see the marching ants around the chart source.

The other two questions should be separate, since they are different issues and it would not be fair to experts to keep them all in one question.

You can ask a related question to keep the connection to this one.

cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
OK, here's #2

starting in C3 on sheet 1

=IF(MOD(MONTH(A3)/3,1),"",SUM(B1:B3))

copy down.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Yearly return: Starting in D12

=IF(MONTH(A12)=12,SUM(B1:B12),"")

You cannot start this formula above row 12, because there are no 12 values to sum and the reference cannot be built.

0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Annualised return:

Starting in row 3 (note that since in this example there is no full year for 1995, the '95 result is not correct.

=(SUMPRODUCT(--(YEAR(DateRange)=YEAR(A3)),--(MONTH(DateRange)<=MONTH(A3)),PerformanceRange)/MONTH(A3))*12

you need to define two dynamic range names

DateRange refers to ='Fund Data'!$A$2:INDEX('Fund Data'!$A:$A,MATCH(99^99,'Fund Data'!$A:$A,1))
PerformanceRange refers to =OFFSET(DateRange,0,1)

Using dynamic ranges will ensure that the sumproduct runs on ALL data in the column and ONLY on the cells with data. This is preferable over using a static range of the highest possible number of entries.
0
 

Author Comment

by:hedgeselect
Comment Utility
Hi

Thanks for the feedback, however some comments.

1. I'm aware of what the error says and the yellow drop down etc, but I really want to understand what it means esp if it can be ignored.

2 & 3 - this almost works, except that your formula is not based on cumulative returns, only arithmetic sum returns.  So the cumulative return for the last 12-months (from Jan 2010 to Dec 2010 should be 4.219%) and Q4 2010 should be 0.7% rather than 0.69%
0
 

Author Comment

by:hedgeselect
Comment Utility
Could I please ask a favour, as this is getting slightly more technical for me.  Could you please update the template accordingly and reupload here for me to try otherwise I'll be messing around and getting it wrong, esp for your last post?

Thanks v. much.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

1. It's not an error. It's a warning. Excel tries to "help" and alerts you that you are not using the adjacent cells in the formulas. You can ignore it if you're positive that your formulas are correct.

2. You know the mathematical background better than I do. Can you explain in words what to calculate?
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Here is the spreadsheet with all the formulas and range names in place as above.

How do I calculate a cumulative return? the Annualised return for December (following your instructions above) arrives at the same value as the Sum() I used.
Copy-of-Master-Statistics-templa.xls
0
 

Author Comment

by:hedgeselect
Comment Utility
Its not quite the same number, I know it looks very close.   I'm not sure how its actually done, I just do it in excel by setting the formula in Column F in worksheet 1 to work out the cumulative value from month to month, but not sure how its done from quarter to quarter to year to year.

Are you able to do the annual returns for worksheet 3 in the summary table for each of the last few years?

Thx.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
so, how do you calculate it from month to month? What's the formula you use?
0
 

Author Comment

by:hedgeselect
Comment Utility
I've posted another question on the following link, hopefully this will provide the solution to your last question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26851873.html

0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
I see that you have opened up another question about how to calculate cumulative return. That's good, because it takes one issue away from this question.

Now, please open another question for the issue #4 above,  because that is another non-trivial subject, which will not be covered in this question. Please make sure to provide reciprocal links.

I believe I have supplied you with the framework to plug the cumulative calculation formula you will receive from your other question into the spreadsheet here.

for quarterly:

=IF(MOD(MONTH(A3)/3,1),"",<the cumulative formula>)

for 12 monthly

=IF(MONTH(A12)=12,<the cumulative formula>)

And I have supplied you with a correct  formula for the annualised return.

On top of that, I have answered your questions 1 and 3. I'm confident you will honor these answers with an appropriate closure of this question.

cheers, teylyn
0
 

Author Comment

by:hedgeselect
Comment Utility
I appreciate your feedback here, but honestly, I could have easily awarded 100 marks for the most simplest one line answer for question 1 and repeated the same with appropriate marks for the others.  It seems more appropiate to award 500 points for a series of "not that difficult but I'm not sure how to do it" type questions.  You refer to ee t&c, I agree but these were not "highly difficult question".

Again I appreciate your answers. Thx.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now