Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with my excel spreadsheet

Posted on 2011-02-27
13
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 50
ID: 34993368
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
ID: 34993393
OK, here's #2

starting in C3 on sheet 1

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

copy down.
0
 
LVL 50
ID: 34993404
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50
ID: 34993442
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
ID: 34993459
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
ID: 34993469
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
 
LVL 50
ID: 34993476
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:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 34993500
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
ID: 34993638
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
ID: 34993750
so, how do you calculate it from month to month? What's the formula you use?
0
 

Author Comment

by:hedgeselect
ID: 34994781
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
ID: 34994798
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
ID: 34994860
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

722 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