+ Reply to Thread
Results 1 to 3 of 3

How to calculate the intersection non linear aggregated curves

  1. #1
    Registered User
    Join Date
    02-01-2019
    Location
    Sofia
    MS-Off Ver
    2018
    Posts
    1

    How to calculate the intersection non linear aggregated curves

    Hello,

    I'd like to find the intersection point of 2 non linear aggregated curves. In the attached spread sheet you can find the raw data with the following data sets:

    - Buy curve:
    -volume
    -price
    - Sell curve:
    -volume
    -price

    The intersection point should be :

    Price 22.55
    Volume 985.5

    How can I get this result in Excel.

    Thank you for your help

    Dimo
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: How to calculate the intersection non linear aggregated curves

    you have more than one value for the same volume
    and there are many missing values
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: How to calculate the intersection non linear aggregated curves

    What I see:

    A) Both curves are "step" functions -- curves that are flat for a span, then increase/decrease in steps. Most algorithms at my disposal do not like step functions.
    B) However, both curves increase or decrease, so there are not multiple intersections.

    My older copy of Solver does not have a good algorithm for this. My first thought, if your newer version of Solver (probably with the Evolutionary algorithm) can handle the "step" nature of the function, I would try something like Solver first.

    1) Guess a price.
    2) Use two lookup functions (approximate match option) to return the volume from each curve associated with that price.
    3) Add a cell to compute the difference or the absolute value of the difference or the difference squared.
    4) Call Solver and tell it to minimize the difference or find the closest value to 0 or similar.
    5) See what solution Solver converges on and evaluate the result.

    That's probably where I would start, if I had your newer version of Excel. Does that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Solver tool: How to find intersection of two curves
    By wilk007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2017, 11:42 AM
  2. How to determine the slope for non-linear curves
    By Wladimir88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2017, 09:42 AM
  3. intersection point of two normal distribution curves
    By gauravsingh in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-03-2013, 10:43 AM
  4. interpolating non-linear curves in excel graphs
    By chris in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 06-19-2006, 10:15 PM
  5. [SOLVED] How can I project the values of intersection of curves in chart on
    By RahulMani in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2006, 11:10 AM
  6. [SOLVED] Followup:Add vertical line at intersection of 2 curves (Ping AndyPope)
    By LeAnne in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-30-2006, 03:35 PM
  7. Add vertical line at intersection of 2 curves
    By LeAnne in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-28-2006, 05:00 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