Solved

Excel statistics template needs fixing

Posted on 2011-03-12
9
236 Views
Last Modified: 2012-05-11
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26878220.html#a35101777

Using the latest template spreadsheet on the above link, please update worksheet 2 (Benchmark indices). Could you please apply the various formulas such that you copy the automatic line zero'ing out, formats and anything else you think worthwhile.  I DO NOT need a copy of all the other columns i.e rolling, annualised etc.

Thanks.
0
Comment
Question by:hedgeselect
  • 5
  • 4
9 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Here you go. Using Excel 2003 I just selected all your data (including the header row (row 3)) and hit Data->List->'Create List' and selected 'My data has headers.' Now if you have any cell in the list selected, you can add data to the next row and it will autofill the whole thing. I didn't get the rolling annualized bit, so I just did the whole sheet. If you mean you want the same thing done to sheet 1, then you should try it so you remember how to do it. (In 2007/2010 I think they replaced 'list' with a revised 'table').
Master-Statistics-template-1-v11.xls
0
 

Author Comment

by:hedgeselect
Comment Utility
Thanks.

1.  I'm using Excel 2007 and for some reason whenever I make any small charges to the first worksheet e.g. cells Q168 and do some other actions, I cannot undo the last few actions.  its as if I have saved the document in the undo history is empty.  Have you come across this problem?

2. Also I've just added a new column K in worksheet 1 called Total return since inception.  This is essentially the same as colulm L, except its an absolute percentage return.  Could someone please update this column so that it both automatically updates everytime a new monthly return is added to col C and K updates automatically but also the blank data before row 91 so that if I add earlier data for new funds going forward, I wont get an error msg.

3.  I also want to add the total returns since inception in the fund summary table in worksheet 3.  What is the easiest way to do this and have it updated dynamically with the latest return information from col K?  I have added the field in the table, just need the dynamic formula added now?

Please use updated spreadsheet attached.


Thanks.
Copy-of-Master-Statistics-templa.xls
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
1. Running macros tends to kill the undo buffer (but not always). I cannot get your sheet to do that to me. Perhaps if it does it again, note the exact steps that you took that made it happen. If I can't recreate the problem, it's hard to diagnose.

2. Not sure what is being asked here. It's a formula so it already updates automatically. If you want to avoid errors for a blank line just add some kind of IF statement. I could help with the construction of such a formula if needed.

3. Is the total return simply the last entry from column K? Just use this formula
=INDEX('Fund Data'!K:K,MATCH(9E+100,'Fund Data'!K:K))
Note: This finds the last entry in the column which is less than 9*10^100 so it works for any numeric data that isn't astronomically large.

Bonus: I noticed you seem to want to format all the December rows a special way. To ensure that you don't miss any, I would use conditional formatting. Apply this formula to the entire sheet
=MONTH($B1)=12
and format accordingly (I used boldface and that funny yellow color).

Administrative comment: General EE policy is to start new questions when you have new questions and to make each question it's own thread. This makes it easier to get each question answered most quickly and well. It also makes point splitting easier and gives the opportunity to be more generous with the points.
Copy-of-Master-Statistics-templa.xls
0
 

Author Comment

by:hedgeselect
Comment Utility
1. I cant replicate the error either now.  It has happened a few times though. maybe after I enabled the macros on the worksheet, cant remember.

2. The whole point of me posting these questions is so that the good folks here can update the spreadsheet for me.  I have no idea what it is I am doing, hence I only describe the desired outcome and hope that the experts will correct implement what it is I am looking for.  

3.  Correct.  Except that if the value is column K is the cumulative return of $1, so say 2.69, then the return is 169%.  In other words, subtract col K by 1 then convert to %.

Thanks for the formatting.  I seem to have lost the red fonts for negatives though for col C,D,E, and F?

I appreciate the EE policy.  Problem is I dont have time to wait to close one question and then open up 4 others after each one is completed.  I'll be here all week.  I'm happy to give extra points to post as separate questions once we can answer here so I can wrap this up by tomorrow.

Do you mind reflecting the above in the spreadsheet attached, esp for col K to auto-complete?  Thanks.


Copy-of-Copy-of-Master-Statistic.xls
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
Comment Utility
Okay. I think I got it.

 Except that if the value is column K is the cumulative return of $1, so say 2.69, then the return is 169%.  In other words, subtract col K by 1 then convert to %
Isn't that what column J is? I think the original formula is correct. Please check again.

Problem is I dont have time to wait to close one question and then open up 4 others after each one is completed.
If you open all of them at the same time, they would probably get answered more quickly. But if you would have trouble merging all the ideas, then I could see why that wouldn't work for you. So if you're happy with how it's working, then it's fine.

I'm happy to give extra points to post as separate questions once we can answer here
Unfortunately, that would be considered a manipulation of the point system and the Admins would delete the questions.
Master-Statistic.xls
0
 

Author Comment

by:hedgeselect
Comment Utility
Hi

Col J is just the cumulative return.  Think of it as the share price start at $1.00 and rising to $3.50 in 1 year.  The performance is not 350% but 250%.

Re: other questions - yes that would be my problem, I wouldn't know or have the time to figure out how to merge all the different question/solutions into one spreadsheet, so happy to open up further questions which would address an already provided solution (but I accept its probably not allowed).........

0
 

Author Closing Comment

by:hedgeselect
Comment Utility
Thanks.
0
 

Author Comment

by:hedgeselect
Comment Utility
Actually, I just noticed that since working on this spreadsheet, the main relative performance chart on worksheet 3 does not fully update the benchmark indicies after you updated worksheet 2.  Could you please check?

Thanks.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
That's because you have two different lists of dates trying to fit on the same chart. Whoever originally set it up used the dates from the first sheet. You would need to split it to two axes. Also, they may all need to start at the same point to work properly.

Master-Statistic.xls
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 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

14 Experts available now in Live!

Get 1:1 Help Now