Method of automatically matching 2 values with a factor

[DH888]

I've created a pair of tables to calculate the CAGR (Compound Annual Growth Rate) over a period of time.

The way I've done it is by doing a first table that uses the actual annual returns to figure out what the compounded dollar value would be based on the actual historic annual returns. A second table matches the last entry which gives me the CAGR percentage it would have taken to achieve that end dollar value. Do you know how I could get excel to automatically match the dollar value in the first table by automatically adjusting the percentage cell until the 2 numbers are a match rather than manually adjusting the percentage cell MANY times manually. (It might be helpful if you saw the tables let me know if you would like to see them).

Thanks

 

[DH888]

omg that's too cool. Thanks so much man I REALLY appreciate it.
And the values are truly equal. I couldn't be bothered to make the cents match.

 

[DRSteele]

Excel, as a rool, is a tool too cool for school.

 

[DH888]

Smile. Excel is definitely one of the coolest productivity applications out there.
That is very cool but it's not exactly as automated as I would have liked since I will still have to change the cell references for each table pair and then do another what-if Analysis for each one. You don't know of a method that would do the same thing without having to rerun the analysis each time I add a new year do you.

 

[DH888]

Say something that would automatically compare the last cells in the 2 relevant columns and generate a factor in the cell at the top of the table. For example

 

[DRSteele]

As I said, you should create macros for each table to avoid having to set up each Goal Seek every table every time, a task which will grow tedious.

I'm thinking that you might benefit from watching an expert illuminate Excel for you. Watch the videos in the ExcelIsFun channel on a regular basis and your level of knowledge will rise rapidly. In this particular playlist, start at #57 and learn all about using Solver to analyse problems just like yours. https://www.youtube.com/watch?v=BM5b4eHiDUo&list=PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ

 

[DH888]

Ideally I would also like to have all the arguments contained in the cell with the generated percentage at the top of the table without the helper cell.

 

[DH888]

You know that goal seek is really blowing my mind. How did excel know to create a percentage that would use all the compounding formulas in the column? The cell at the top that houses the percentage does not refer to any other cells so how did excel instantly calculate a CAGR percentage that used the table I created? It's seriously mindblowing. It must have seen that the cells in the column below the percentage cell referred to that cell.

I mean seriously from the little information I provided it 3 little numbers it figured out an exact number that made those 2 numbers match by compounding an investment to the exact same number as the one I specified. Using a table that I did not specify to use. Truly a magnificent application.

 

[DH888]

Hi DrSteele I installed the HTMLMaker you provided and now every time I open the VB editor there are 3 windows open ON TOP of all the other code I have added to a spreadsheet. This is INCREDIBLY ANNOYING. I have to constantly shut these 3 windows which I WILL NEVER need to access since I will never modify them so there is absolutely no reason they should ever be open. I would like to have the functionality available that this code provides but if I have to close these 3 windows every time I open the VB Editor it's just not worth it. Do you know how I can have the code for these 3 windows start in a closed state.
Thanks

 

[DRSteele]

No, I don't know. But I suggest you click Developer, click on Excel Add-ins, click to remove the check-mark next to HTMLmaker and click OK. Next time you wish to upload code to this forum you can re-enable it by adding the check-mark, closing Excel and then opening Excel.

 

[DH888]

Thanks that solved it for me I appreciate it.