Goal Seek with variable Set Cell

Discussion in 'Excel Beginners' started by raith99, Aug 8, 2013.

  1. raith99

    raith99 Guest

    Hi,

    I'm relatively new to excel and would appreciate any help with th
    following problem.

    I'm working on a pensions spreadsheet and need to work out income a
    retirement over a set period. I have tables which have all the require
    formulas and a manual goal seek works fine if I enter the set cell o
    the corresponding period entered by the customer. Unfortunately th
    period can change, i.e. some may want the income for 15 years, others 2
    years or whatever.

    I realise to automate this I need a macro but I don't know how to d
    this with a variable set cell. I've tried with vlookup but that doesn'
    work. I've worked out a formula to give me the location of the set cel
    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
     
    raith99, Aug 8, 2013
    #1
    1. Advertisements

  2. raith99

    joeu2004 Guest

    "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
    MediaFire: http://www.mediafire.com
    FileFactory: http://www.filefactory.com
    FileSavr: http://www.filesavr.com
    RapidShare: 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
    news:...
    >
    > 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
     
    joeu2004, Aug 8, 2013
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. P. Nogueira

    Reach a Goal.

    P. Nogueira, Oct 17, 2004, in forum: Excel Beginners
    Replies:
    5
    Views:
    133
    VENKAT
    Oct 23, 2004
  2. Mark

    goal seek command function

    Mark, Oct 21, 2004, in forum: Excel Beginners
    Replies:
    2
    Views:
    73
    VENKAT
    Oct 21, 2004
  3. Alberta K.
    Replies:
    3
    Views:
    89
    RagDyeR
    Dec 19, 2007
  4. TheFluffy

    How do you put Goal seek on a toolbar?

    TheFluffy, Jan 4, 2008, in forum: Excel Beginners
    Replies:
    2
    Views:
    240
  5. TheNewGuy

    Dividing a linear goal

    TheNewGuy, Mar 13, 2008, in forum: Excel Beginners
    Replies:
    2
    Views:
    90
    TheNewGuy
    Mar 13, 2008
  6. Paulo

    vba GOAL SEEK

    Paulo, Jun 2, 2008, in forum: Excel Beginners
    Replies:
    1
    Views:
    74
    J Sedoff
    Jul 28, 2008
  7. Laura

    Please Help With Goal Seek

    Laura, Aug 2, 2008, in forum: Excel Beginners
    Replies:
    6
    Views:
    123
    Ken Wright
    Aug 3, 2008
  8. Piper

    Can't Figure Out Goal Seeking. Help!

    Piper, Dec 27, 2012, in forum: Excel Beginners
    Replies:
    2
    Views:
    160
    Piper
    Dec 28, 2012
Loading...