Solved

Dynamically updating ranges in Excel - please update my spreadsheet

Posted on 2011-03-06
17
515 Views
Last Modified: 2012-05-11
Could someone who is good at working with dynamic cell referencing, please make changes to the attached spreadsheet so that as new monthly performance return data is added to column B on worksheet 1 and columns B, E and H on worksheet 2, the following is dynamically updated:

In the summary statistics table on worksheet 3:

1. Each of the 4 lines in the chart are dynamically updated so that I don't need to do anything when a new monthly return is added to worksheets 1 and 2

2.  Cells C21 to C24 and  C26 to 27 need to also dynamically update with the new additional monthly returns

Thanks.

 Master-Statistics-template-1.xls
0
Comment
Question by:hedgeselect
  • 10
  • 7
17 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35048420
Here's you dynamically updated graph - I created an additional one so you can compare.  There are three dynamic ranges:  FUND_1, BM_1, and BM_2 that handle the dynamic address changes, addressing the first to last occurances of values in each of the respective ranges.


See attached,

Dave
Master-Statistics-template-1-r1.xls
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 35048442
I also added the lastReturn named range to find a variable neede for your table.

Your table is now updated as well

Dave
Master-Statistics-template-1-r2.xls
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 35048592
last touch - I updated the text labels in the table "Since ..." to show the Month/Year of the first dataset.

To do all this, I created a RANGE NAME using the OFFSET FORMULA as follows (for FUND_1):

=OFFSET('Fund Data'!$F$1,MATCH(TRUE,INDEX('Fund Data'!$B$4:$B$1000<>"",0),0)+ROWS('Fund Data'!$B$1:$B$3)-1,0,COUNTA('Fund Data'!$B$4:$B$1000))

And, one for BM_1, and BM_2 as well, along the same lines.  The OFFSET formula can return a RANGE, and this on looks from the FIRST occurance of data in column B to the LAST occurance of data in Column B (uses the COUNTA function to determine how far down to go to get to the LAST occurrance.

So:

=OFFSET('Fund Data'!$F$1 <-where to start looking

  MATCH(TRUE,INDEX('Fund Data'!$B$4:$B$1000<>"",0),0) <- searches from B4 (after headers) to B1000 - up to 1000 records - searching for nonblank

    ROWS('Fund Data'!$B$1:$B$3)-1 <- corrects for the starting row offset of B4
     
    COUNTA('Fund Data'!$B$4:$B$1000) <- returns the HEIGHT of the resulting range (# rows), based on having data in this column.

Cheers,

Dave
Master-Statistics-template-1-r3.xls
0
 

Author Comment

by:hedgeselect
ID: 35048893
ah, this is truely awsome!  I've never been so excited at the thought of my spreadsheets updating like this!  
Thank you so much :)...and you have gone beyond the call of duty with the extra bits, which actually I had in mind but didnt ask, so thanks again.

I'll be honest and say that I honestly dont know what all that code means or how it works, and to be honest, I dont care as long as it works (though I would love to try and understand it when I get the time).

1. Could I please ask you to check cell C26 on the stats table as it does not appear to update the Negative Months:Total Months when I throw in more monthly numbers?

2. Also I see that the new chart you created, the x axis is based on number of months.  For comparison purposes, is how easy and quick would it be to create a replica of my existing chart with the same title axis etc, colour, etc as I need to stick to my format?

Truly awsome again - how long did this take you to do?

If you feel up to it, please feel free to use your final version template after the last update above, to reflect rolling returns if you wish to participate on http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26839152.html

I will shortly close this question off and award you the points.

Top man!

0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 35049395
I've used this approach many times in helping others debug their formulas.  It took about 15 mins because I made a few mistakes.  if you save a sample then its pretty easy to modify...

Whoops - I missed 26 and 27 (blushing...)

I just inserted the named range:  FUND_1 in the formula as a substitute to your hard-coded formulas.

I updated your chart and got rid of mine :)

I did this by editing the chart, then editing the series and replaced the RANGE with FUND_1, BM_1, BM_2, and BM_3 and the X-Axis range to DATER_1

See attached.  I need to figure out how to record what I do so I can make a video and post, lol :)

Dave


Master-Statistics-template-1-r4.xls
0
 

Author Comment

by:hedgeselect
ID: 35049612
Hi

hmmmm..not sure what happened but:

1. C26 on the stats table still not updating the negative months but total months is increasing correctly
2. C27 on the stats table is giving a wrong figure - it should be taking the highest monthly loss i.e. on col B first worksheet
3. Chart not correctly updating with monthly dates when I dump more monthly fund data

....nearly there though.  I'll check again in the morning, off to sleep now.  hard work verifying this :)

Cheers.
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 35049853
1.  Sorry, I was looking at the cumulative not monthly performance - corrected now by adding FUND_M1 looking at column B.

2.  Also using FUND_M1

3.  After adding data to the BM rows and FUND rows, the chart does indeed update :)  The Date range is tied to the FUND DATA sheet - so if you just update that, you'll see the new dates...  Also, you need to add a quarter's worth of data to see a new date, the way your chart is setup.  I tested and it works...

Cheers,

Dave
Master-Statistics-template-1-r5a.xls
0
 

Author Comment

by:hedgeselect
ID: 35053610
Hi

Ok I think we're almost done now.  Just on point 3, I have added more data to both the fund and the three benchmarks and whilst the date range does indeed update, the Fund line does not, it's stuck Feb 2011?

As more data is added to the tables and the date increases, what do you recommend should be done about the date range on the chart as it will get more "cluttered".  Is there a way of saying, "after x number of monthly data points, goto semi annual  reporting instead of quarterly and if there is less than 12 then keep it as quarterly"?


Master-Statistics-template-1-v2.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 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 35058907
For some reason (probably my operator error) the range FUND_1 was not in the chart - it was the hardcoded absolute addresses, lol.

As far as date ranges go...  if you leave the major/minor unit for x-axis as "Auto", the dates should not get cluttered.  If you have a general rule and want to do the processing, that could be added.

Try it on auto for now to see how it performs then get back to me.

Dave
Master-Statistics-template-1-v2-.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35059096
On the event _Activate of Sheet3 - where your graph is, I added this code:

 
Sub SetXAxis()
Dim i As Long

    i = [FUND_1].Rows.Count
    
    Sheet3.ChartObjects("Chart 5").Select
    With ActiveChart.Axes(xlCategory)
        If i <= 12 Then
            .MajorUnit = 1 'do monthly
        ElseIf i <= 24 Then
            .MajorUnit = 3 'do quarterly
        Else
            .MajorUnit = 6 'do semiannually
        End If
    End With
End Sub

Open in new window


Which sets the x-axis major unit to 1 for monthly if months <=12, and to 3 for quarterly if > 12 but <=24, and then for 2 to semi-annual otherwise.

Pls see attached.

Dave
Master-Statistics-template-1-v2-.xls
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 35059123
Sorry - ignore that last post.  Here it is revised (I had posted the file before I saved the final solution!)

Cheers,

Dave
Master-Statistics-template-1-v2-.xls
0
 

Author Comment

by:hedgeselect
ID: 35063350
Hi Dave,

Thanks again for your efforts.

To be fair to you and to help you move up the rankings, I'm going to close this question and award you the points, but would like to open up another (and possibly more) questions to make further modifications to this excel chart along the same sort of lines of what you are capable of doing.

My preference would be for you to do it since a) you're fast and b) you're good and c) you know what I want.  So consider this question closed, but what I now want is:

1. Two new columns in worksheet 1 (Fund Data worksheet), perhaps between existing columns E and F which look at a) Year To Date (YTD) returns and b) the other column to have 12-month rolling returns (both based on the monthly fund data).

Just to be clear, the YTD returns will always show the cumulative return in that year, so for 2011, it will be Jan 11 and Feb 11.  For complete years the YTD will match the Yearly returns.  The rolling returns will fill every cell in the new column and also look at the cumulative returns for exactly a 12-month period regardless of what the current month is.

If you are happy to work on this, I thought maybe if you want to work on it here and when done, post the spreadsheet here and I can open a new question and you can just post the spreadsheet there too so it makes it easier to award you the points?

As with the rolling 12-month chart from the other question, are you able to include that chart in this spreadsheet here such that it is a second chart in the third worksheet?  I'll obviously close that question off and this one at the same time and award the points.

Thx again.
0
 

Author Comment

by:hedgeselect
ID: 35063361
Oh, and why do I get those green little arrows in the cells on worksheet 1 i.e. cell errors.  How to get rid of those and still have the formulas copy up and down?  I need to make sure that the empty cells at the top of the B to F will work correctly if I ever dump data in there for funds that have longer track records i.e. more data points prior to Feb 08.

Thx.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35063749
ok - just refer to this question when you create the new question so I'll see it.  I can add the 12 month option to this one...

Cheers,

Dave
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 35065305
I added the monthly chart to the last sheet, and listed the ranges created to do that on that last sheet

1.  I added 3 columns to the Fund Data Tab:

a.  G column expressing $1 invested on an annual basis (reverting back to $1 each January)
b.  H column expressing return on a YTD basis using that new G column data point (which is the G number less 1 for return on a YTD basis)
c.  I column expressing last 12 month return on a rolling basis, using the $1 invested since inception column and just taking the current minus 12 months earlier to get the rolling average return.

Check me on this, as I did it fairly quickly, but I think its right...

The green arrows at the upper left hand corner of the cells are just telling you that the formula you created don't consider adjacent cells.  I generally get these when
the style of formula is inconsistent with what I've used in adjacent cells.  These are not necessarily errors and you can clear them by clicking the popup with the yield
sign exclamation character and say "Ignore this error".  I don't see anything obviously wrong with your formula, and it actually seems right, so I'd ignore if I did the calc
manually and attested to its veracity.

Cheers,

Dave
Master-Statistics-template-1-v3.xls
0
 

Author Comment

by:hedgeselect
ID: 35066184
Hi Dave,

Please continue this conversation on a new question, posted (to be clear) as I have asked further questions on this topic.  I will now close this q.


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

Thanks.

0
 

Author Closing Comment

by:hedgeselect
ID: 35066188
Top marks for Dave, excellent solution!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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