Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 8th 13, 07:08 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2013
Posts: 4
Question 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 (1-30)
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   Report Post  
Old August 8th 13, 09:47 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 829
Default 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 file-sharing website. The following is a list of some free
file-sharing 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 (1-30)
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 (1-30)
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Goal seek on more than one cell David Wess Excel Discussion (Misc queries) 1 June 16th 08 01:31 PM
Goal Seek with dynamic Goal Seek Dkline Excel Programming 1 February 18th 08 10:00 AM
How can I put Goal Seek into a cell formula? ecalexan Excel Worksheet Functions 4 December 1st 06 08:43 PM
Goal Seek Mike Excel Worksheet Functions 3 January 23rd 06 04:39 AM
Goal Seek - reference a cell for "To value" field? cchristensen Excel Worksheet Functions 1 November 5th 04 07:27 PM


All times are GMT +1. The time now is 05:18 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017