Home 
Search 
Today's Posts 
#1




Goal Seek with variable Set Cell
Hi,
I'm relatively new to excel and would appreciate any help with the following problem. I'm working on a pensions spreadsheet and need to work out income at retirement over a set period. I have tables which have all the required formulas and a manual goal seek works fine if I enter the set cell of the corresponding period entered by the customer. Unfortunately the period can change, i.e. some may want the income for 15 years, others 25 years or whatever. I realise to automate this I need a macro but I don't know how to do this with a variable set cell. I've tried with vlookup but that doesn't work. I've worked out a formula to give me the location of the set cell but again have no idea how to get this to work with a goal seek macro. Column A has the number of years (130) Column B has the annual income Column C has the total fund value So if required period 15 years the Set Cell would be C15, if 20 years, C20, etc. Goal Value would be 0 Changing Cell would be B1 Hope you can help before I pull all my hair out! 
#2




Goal Seek with variable Set Cell
"raith99" wrote:
I'm working on a pensions spreadsheet and need to work out income at retirement over a set period. I have tables which have all the required formulas and a manual goal seek works fine You might not need to use Goal Seek at all. It depends on what your variables are. I suggest that you post the URL of an example Excel file that you uploaded to a filesharing website. The following is a list of some free filesharing websites; or use your own. Box.Net: http://www.box.net/files Windows Live Skydrive: http://skydrive.live.com MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com RapidSha http://www.rapidshare.com "raith99" wrote: Column A has the number of years (130) Column B has the annual income Column C has the total fund value So if required period 15 years the Set Cell would be C15, if 20 years, C20, etc. Goal Value would be 0 Changing Cell would be B1 Assuming you are familiar with writing VBA macros, try the following code fragment. I assume that data starts in row 1, and the number of years is 2 or more. Dim lastRow As Long lastRow = Range("A1").End(xlDown).Row Range("C" & lastRow).GoalSeek Goal:=0, ChangingCell:=Range("B1")  original message  "raith99" wrote in message ... Hi, I'm relatively new to excel and would appreciate any help with the following problem. I'm working on a pensions spreadsheet and need to work out income at retirement over a set period. I have tables which have all the required formulas and a manual goal seek works fine if I enter the set cell of the corresponding period entered by the customer. Unfortunately the period can change, i.e. some may want the income for 15 years, others 25 years or whatever. I realise to automate this I need a macro but I don't know how to do this with a variable set cell. I've tried with vlookup but that doesn't work. I've worked out a formula to give me the location of the set cell but again have no idea how to get this to work with a goal seek macro. Column A has the number of years (130) Column B has the annual income Column C has the total fund value So if required period 15 years the Set Cell would be C15, if 20 years, C20, etc. Goal Value would be 0 Changing Cell would be B1 Hope you can help before I pull all my hair out!  raith99 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Goal seek on more than one cell  Excel Discussion (Misc queries)  
Goal Seek with dynamic Goal Seek  Excel Programming  
How can I put Goal Seek into a cell formula?  Excel Worksheet Functions  
Goal Seek  Excel Worksheet Functions  
Goal Seek  reference a cell for "To value" field?  Excel Worksheet Functions 