+ Reply to Thread
Results 1 to 6 of 6

Changing the value in one cell to make two other cells equal (Goal Seek?)

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Changing the value in one cell to make two other cells equal (Goal Seek?)

    Hello all, hoping for some help on this problem. Apologies if the title is not very descriptive, but I'm not sure how to describe this....

    I have a spreadsheet that calculates stock trading signals using the daily stock price data as the input data.

    I column C is the daily "high" price. Increasing the value in one row of column C causes (via a series of intermediate calculations) the figure in column M in the same row to increase, and the figure in column N to decrease. Initially, the value in N is higher than M, but I need to calculate the reversal point. At a certain value in column C, the values in column M and N will be equal - this is effectively the "crossover" point, where M becomes higher than N.

    I can of course find this value of C by plugging in numbers using trial and error, but I need to do this multiple times and so want to automate it.

    Can this be done automatically in Excel? I can't use Goal Seek since there is no fixed value for M or N, the "goal" is to make M and N equal.

    Any help would be greatly appreciated! I don't really want to post the spreadsheet as it's huge and somewhat confusing!

    Thanks!

  2. #2
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Changing the value in one cell to make two other cells equal (Goal Seek?)

    Looks like you have more like a math problem. Could you post the formulas that calculate the values in M and N?
    When I say semicolon, u say comma!

  3. #3
    Registered User
    Join Date
    08-30-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Changing the value in one cell to make two other cells equal (Goal Seek?)

    I've attached a stripped-down version of the spreadsheet here, taking out most of the data to keep it manageable...

    In row 119 I've highlighted the relevant cells (I got my columns wrong in the original post, it's actually C, L and M.)

    In the previous row (118), column M is higher than column L. Increasing the value in C119 will cause L119 to increase and M119 to decrease, until L119 and M119 cross-over. The value in C119 in the spreadsheet is higher than it needs to be to get L119 and M119 to crossover. What I'd like to do is have some way of finding out what value in C119 will cause M119 to equal L119. (from trial and error, it's 1718.55, but I would like to have excel calculate this for me).

    Hope this makes sense!
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Changing the value in one cell to make two other cells equal (Goal Seek?)

    Hmm it's not the math problem I expected. If M119 and L119 were (somewhat) directly dependent on C119, I could have solved it.

    Maybe an array can do the 'trail and error'-ing for you, but I don't know arrays

  5. #5
    Registered User
    Join Date
    08-30-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Changing the value in one cell to make two other cells equal (Goal Seek?)

    Well thanks anyway for trying. It's got me perplexed for sure... I'm wondering if I could use some sort of interpolation technique to get an approximate answer (which would be better than nothing), but can't figure it out....

  6. #6
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Changing the value in one cell to make two other cells equal (Goal Seek?)

    You can, but then you'd have to reconstruct the formulas in M119 and L119 all the way to C119. But L119 and M119 depend on I119 and J119 and K119, which depend on I118 and J118 and K118 and F119 and G119 and H119, which depend on... etcetera...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing formulas based on one cell (period) in worksheet (Goal seek, scenerio?)
    By workingmom434 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-19-2013, 07:34 AM
  2. Using goal seek to make a chart
    By AKK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2007, 09:46 PM
  3. [SOLVED] Goal Seek - Why make the PV negative?
    By Mytpoet67 in forum Excel General
    Replies: 1
    Last Post: 01-12-2005, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1