Percentage Increase in A Period

Jessi88

New Member
Joined
May 29, 2008
Messages
5
I'm doing research with a professor at the University of Florida. This is my first time using Excel to any extent, so I need everything from the ground up!

I have the housing values for each of the 50 states from 1975 to 2006. I need to analyze each state individually to see if there were any points during that 26 year span when housing values increased by more than 20%. I need to know when this increase started and when values peaked and began to fall. I could do this manually, but I'm assuming that would take a while.

Is there a simple way to do this in excel? A macro, or a UDF (I have no clue about either of them by the way).

Thanks so much!

The 50 states are listed in a column and the rows next to each state having housing values for the 26 years. ( I don't know if that is relevant).;)
 
Lol. No offense taken. I'm sure I wouldn't. I'm definitely a novice. Compound is what I would need.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Homework, eh? Tsk, tsk.
icon12.gif


Okay, I'm sending you the example.

I'll probably still whip up a VBA example tomorrow, because it's an interesting problem.

But don't hand that in if your prof knows anything about VBA, it's probably going to be obvious that you didn't write it.
 
Upvote 0
Hello again,

Well, it doesn't quite do that. You're talking compounding growth from 1985-1990? And you want to do that scenario for each state for every possible combination of years?

That would require a ton of iterations on each state. It's doable, and my CAGR example is how you do it, but I didn't set it up to easily do that. You'd need a pretty big table for each state (unless you did this with VBA) to accomplish this. I suppose I could do a UDF...
It does now. It's alive! :cool:

This is somewhere between the craziest and most interesting questions I've ever seen asked here, imo. Did someone in good faith ask you to do this by hand for every possible combination of years? That would have to take you at least a full working week, if not longer, to figure out.

You could do this with Worksheet Functions, by setting up an Amortization table like structure for every possible year-combination, but you'd end up with an unmanageable amount of data for each state, it would be unwieldy, at best.

So, it's UDF time. This has to be used on data that's cross-tabbed, you need years across the header row, and like you said, a column of states and the principal amounts in the columns that follow.

So, in a normal module (Alt-F11->Insert->Module) place the following UDF:

Code:
Public Function CAGRThresh( _
    ByRef rngYears As Range, _
    ByRef rngPrincipal As Range, _
    ByVal curThresh As Currency) As String
Dim varYears() As Variant, varPrincipal() As Variant
Dim strRet() As String
Dim i As Long, j As Long, lngCount As Long
Dim lngUpper As Long
Dim curCAGR As Currency
Let varYears = rngYears.Value
Let varPrincipal = rngPrincipal.Value
Let lngUpper = UBound(varPrincipal, 2)
ReDim strRet(1 To (lngUpper ^ 2 * 0.5 + lngUpper * -0.5))
For i = LBound(varPrincipal, 2) To lngUpper - 1
    For j = i + 1 To lngUpper
        Let curCAGR = ((varPrincipal(1, j) / _
            varPrincipal(1, i)) ^ (1 / (j - i))) - 1
        If curCAGR >= curThresh Then
            Let strRet(lngCount + 1) = varYears(1, i) & _
                "-" & varYears(1, j) & ": " & _
                Format$(curCAGR, "0.00%")
            Let lngCount = lngCount + 1
        End If
    Next j
Next i
If lngCount > 0 Then
    ReDim Preserve strRet(1 To lngCount)
    Let CAGRThresh = Join$(strRet, ", ")
    Else: Let CAGRThresh = "N/A"
End If
End Function
And yes, I did hurt my brain writing this, but a fascinating question... Even figuring out the maximum size of the return array was an interesting endeavour! :eek:

And use in a Worksheet as such:

=cagrthresh($B$1:$G$1,B2:G2,20%)

Where B1:G1 is your header row with dates, B2:G2 has the principal amounts per state and 20% is your threshold (change as necessary). If you use dollar signs (absolute referencing) on the header row (dates), you can now copy down. Your header row needs to be the exact same number of columns as your principal columns, or be assured this won't work, as written.

This returns a potentially long string of periods with CAGRs that meet or exceed your stated threshhold. The format is as follows:

Year X-Year Y: CAGR, Year X-Year Y:CAGR, etc...

E.g., here's one return on my tests:

1991-1993: 36.93%, 1992-1993: 50.00%

I couldn't think of a better summary format... And you can use Text-to-Columns to parse this, if you want (: and , being your delimeters).

It returns every single possible annual combination and the CAGR for every combination where the CAGR is equal to or greater than your threshold. It's pretty **** fast too, a nice benefit. There's a week's work done in about a millisecond.

If there are more questions or you're having trouble implementing this, let me/us know. I had better get an A for this one, one of the cooler algorithms I've written in a while.
icon12.gif
:biggrin:
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top