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).;)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hello, welcome to the board. :)

Sounds like you want to use the CAGR, e.g.,

http://mrexcel.com/forum/showthread.php?p=86394

The function you want is in G17/Function bar. Probably insert a row for each state, make it cumulative (using absolute references and variable references) and copy accross. Or maybe even better, just start a new grid, down below.

If you need more help, let us know.
 
Last edited:
Upvote 0
I am so utterly lost. I have no idea what any of that means...
I'm really sorry. Is there a different forum for beginners?

...the CAGR will search all the housing values within a particular state and automatically tell me which periods had a 20% or more increase?
 
Upvote 0
No, sorry Jessi, I'm afraid you're stuck with me in the deep end. :)

I can't tell by what you mean by periods. Did you want to compare cumulatively or year-over-year? See the following:

(I had to delete this, see the attached file)
<DELETED spreadsheet>

This is all on the same sheet, I'm having trouble posting it as such.

Note, the first box replicates your list of values (smaller). The second box looks at cumulative growth, so if you look at 1992 in the 2nd box, it's saying how much has this thing grown, at a compound rate since 1990. And the third box just looks at year-over-year growth.

That's how I would set it up, while yours would be bigger. From there, a little Conditional Formatting and your >20% returns will be lit up like a Christmas Tree. :biggrin: ;)
<CENTER><CENTER><CENTER>
Edit: Can't get it to paste... See the attached file.​
</CENTER></CENTER></CENTER>
 
Last edited:
Upvote 0
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).;)

Hi Jessi88:

Welcome to MrExcel Board!

What you have in your hands is a project. I suggest you consider the following ...

1) develop an outline or specification of what you are trying to accomplish
2) naturally you will have to know how to do it manually
3) EXCEL can do the repetitious and tedious work of number crunching for you -- you have to provide the formulation
4) how are you going to present your results -- tabulating, charting/graphing, combination
5) do, say 1 state covering a span of a few, say 5 years ... so you can develop your methodogy
6) If you can do 1 state covering a period of 5 years, you will be able to extend your work to 50 states, covering 26 years, or 32 years as the case may be
7) and as you go along, you will discover you will have to make some course corrections
8) working out just 1 state for a few years will not be a daunting or overwhelming task
9) working on 1 state for a few years, you will also hone your EXCEL skills to some extent -- that will boost your confidence as well as your productivity
10) ...

My Ten point list in the preceding is not meant to discourage/scare you, nor is it a cop out from my side to not answer your question. You need more than a formula or a UDF ... you need to start with a strategy for your project.
 
Upvote 0
Sorry, I was having a bear of a time posting an HTML example, see if the attachment (in my last post), CAGR.zip, helps.
 
Upvote 0
Thanks! It actually won't allow me to open the zip file; apparently I don't have sufficient privilages.

By periods I mean this. For Alabama, for instance, I have housing values for 1975-2006. If in any time frame(s) the housing value increased by 20% or more in Alabama, I want to know that time frame. Say for instance it increasd by 21% between 1985 and 1990 (started to rise in 1985 and housing values peaked before falling in 1990). I need to know that time frame. If it happened in multiple time frames, I need to know all of them.

I have to know all of these time frames for all 50 states, because then I have to do some more work with them comparing them to what happened to income, property taxes, etc. during those time frames.

Sorry if anything isn't clear. I really appreciate your help and patience by the way. :)
 
Last edited:
Upvote 0
Really? I tested opening it, from here. Were you getting some sort of error message?

My example does exactly what you want it to do, it just be a matter of figuring out whether you want cumulative growth or year-over-year growth, there's examples of both in the Workbook. It just be a matter of tweaking the ranges and copying and pasting, a 2 minute job.

Do want me to email it to you? Don't post your email address, I (as an Admin) have access to it. Yes or no will suffice.
 
Upvote 0
Say for instance it increasd by 21% between 1985 and 1990 (started to rise in 1985 and housing values peaked before falling in 1990). I need to know that time frame. If it happened in multiple time frames, I need to know all of them.
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...

Maybe I'll whip a VBA example tomorrow (I need to run out tonight), but I'm guessing, nothing personal, you won't understand it or be able to modify it...
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
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