+ Reply to Thread
Results 1 to 10 of 10

Check if a point is outside a curve

  1. #1
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Check if a point is outside a curve

    Greetings!
    I have a curve which looks like so:
    graph.png
    A point will be plotted - I want to see if it falls outside the curve.
    For example, the following point should turn up as outside the curve:
    graph.png
    Unfortunately the curve is not described by a mathematical equation - it uses the following points:
    Capture.PNG
    Any suggestions? I have a potential idea which I'm going to try to implement but it's long, awkward and irritatingly mathematic.
    Thanks!
    Lady Marmalade

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Check if a point is outside a curve

    Can you post an Excel sheet? a) your photos are invisible (to me, at least), and b) there's not much we can do with a screenshot, anyhow...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Re: Check if a point is outside a curve

    I actually just managed to solve it -
    http://andypope.info/charts/intersection.htm
    That returns coordinates (or #N/A if they don't exist) of intersection points between a line segment and a set of scatter graph points.
    If I invent a line between my point and the x-axis and they don't intersect with the curve then voila, it's under the curve.
    Thanks though!

  4. #4
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Re: Check if a point is outside a curve

    Quote Originally Posted by Glenn Kennedy View Post
    Can you post an Excel sheet? a) your photos are invisible (to me, at least), and b) there's not much we can do with a screenshot, anyhow...
    Well, I was wrong about my purported solution. I couldn't get it working (I think it doesn't work for logarithimic situations).

    Attached is a spreadsheet which should help -
    Example.xlsx

    I did find the following: http://www.excelforum.com/excel-gene...ersection.html, but could not make heads or tails of it.

    Thanks,

    Lady Marmalade

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

    Re: Check if a point is outside a curve

    I'm not sure what "outside" or "inside" means in a case like this, though I can make sense of "above" or "below".

    I'm not in a position to look at your sample spreadsheet, so my response cannot take advantage of anything you have done so far. I see two basic approaches to a problem like this: interpolation (usually linear) or regression.

    You mentioned a "logarithmic situation" (whatever that means to you) which suggests that you might have some idea of what kind of function these data should fit. If so, one possible approach will be to regress an equation, then compare the data point to that equation. If your function is "linear" [g(y)=a1*f1(x)+a2*f2(x)+a3*f3(x)+...], then you can use the LINEST() function to perform the regression quite readily.

    Even though Excel (unlike quattro pro) does not offer a built in linear interpolation function, it is fairly easy to use Excel's lookup functions to perform a linear interpolation on the data. It should then be easy enough to compare this "stepwise" function through the plotted data points to the point in question and determine above or below.

    Due to uncertainties in either approach, there will almost certainly be a band around the "curve" that is too close to determine, no matter what "is y0<f(x0)" returns.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Re: Check if a point is outside a curve

    I'm not sure what "outside" or "inside" means in a case like this, though I can make sense of "above" or "below".
    Aha, yes, I did realize afterwards that this graph is kind of upside down. I didn't actually make it, just trying to automate someone else's work a bit.

    You mentioned a "logarithmic situation" (whatever that means to you) which suggests that you might have some idea of what kind of function these data should fit. If so, one possible approach will be to regress an equation, then compare the data point to that equation.
    Well...no idea what kind of function the data should fit. I just know that the y-axis is logarithmic.

    If your function is "linear" [g(y)=a1*f1(x)+a2*f2(x)+a3*f3(x)+...], then you can use the LINEST() function to perform the regression quite readily.
    The example I quoted uses LINEST() but as I mentioned, I couldn't make heads or tails of it (He was using logarithms on things before it even entered the LINEST() function so I wasn't sure where he was going. I'll sit down and put more effort into understanding it >.<)

    Due to uncertainties in either approach, there will almost certainly be a band around the "curve" that is too close to determine, no matter what "is y0<f(x0)" returns.
    Where do these uncertainties arise? The person in the quoted example used LINEST() to get exact coordinates of intersection between curves/lines, so surely I can do something similar?

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

    Re: Check if a point is outside a curve

    The example I quoted uses LINEST() but as I mentioned, I couldn't make heads or tails of it (He was using logarithms on things before it even entered the LINEST() function so I wasn't sure where he was going. I'll sit down and put more effort into understanding it >.<)
    That particular example by shg uses a power function [ln(y)=ln(m)+b*ln(x) note that this fits the general form of a linear function I gave earlier] to fit each pair of points. Applying this kind of approach to your situation amounts to an interpolation approach. We can regress a function (power function in the example) for each pair of points. Then use a lookup function to get the parameters for each piece of the function, then find our estimate of y(equation) at x0, then compare y(eqn) to y0 and see if y0 is above, below, or equal to y(eqn).

    Does that make sense?

    Where do these uncertainties arise? The person in the quoted example used LINEST() to get exact coordinates of intersection between curves/lines, so surely I can do something similar?
    The computer calculates an exact (to within rounding error) number, but everything leading up to it has an uncertainty to it that the computer cannot see. These uncertainties come from the uncertainty in the tabulated data (is F really exactly 1.000000000e-6 when N is exactly 70.00000000000?) and our uncertainty in exactly how the function behaves between data points. I am obviously not in a position to give you a thorough discussion around error analysis, though any good statistics course should help you understand that. Anytime we perform these kind of analyses, we need to be somewhat aware of the uncertainty in the analysis.

  8. #8
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Re: Check if a point is outside a curve

    Quote Originally Posted by MrShorty View Post
    That particular example by shg uses a power function [ln(y)=ln(m)+b*ln(x) note that this fits the general form of a linear function I gave earlier] to fit each pair of points. Applying this kind of approach to your situation amounts to an interpolation approach. We can regress a function (power function in the example) for each pair of points. Then use a lookup function to get the parameters for each piece of the function, then find our estimate of y(equation) at x0, then compare y(eqn) to y0 and see if y0 is above, below, or equal to y(eqn).

    Does that make sense?
    Where exactly does a lookup function become involved?

    Quote Originally Posted by MrShorty View Post
    The computer calculates an exact (to within rounding error) number, but everything leading up to it has an uncertainty to it that the computer cannot see. These uncertainties come from the uncertainty in the tabulated data (is F really exactly 1.000000000e-6 when N is exactly 70.00000000000?) and our uncertainty in exactly how the function behaves between data points. I am obviously not in a position to give you a thorough discussion around error analysis, though any good statistics course should help you understand that. Anytime we perform these kind of analyses, we need to be somewhat aware of the uncertainty in the analysis.
    Ah yes, I thought it'd be more to do with the latter - in this case, the tabulated values from which the curve is based are not from experimental data. It's sort of a criteria of acceptability - so if it's within the criteria (below) the criteria, it's acceptable, if it's not (above) the criteria, then it isn't. Therefore, there's no issue of their accuracy. Not sure exactly where they originate from, but that's a factor I know I need not worry about.
    Last edited by MarmaladeLover; 11-18-2014 at 11:00 AM.

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

    Re: Check if a point is outside a curve

    Where exactly does a lookup function become involved?
    After performing the regression for each pair of points, you will have a lookup table that can be used to find the regressrion equation that you will use for the region between each tabulated point. I might set this up as:

    1) C:D =LINEST(LN(B2:B3),LN(A2:A3)) (note array entry here) This will return a curve that connects the first two data points.
    2) copy this down until the 2nd to last entry in the table (note the use of relative references so that each regression is for one pair of points). The last entry in the table will error because there is not a point below it.
    3) At this point, you will use lookup function(s) based on your x0,y0 point to pull the parameters for the segment of the curve that you are interested in.

    I've attached my best attempt at an example. This isn't the only way to do it, but it illustrates how it could be done.

    I've included another point at 150 to illustrate what I mean be uncertainty. Assuming the algorithm is exact, the computer will say that 150 and 1.99e-6 is above. But, as I look at it, it is pretty close to the curve, to the point that I would wonder if we can really state that it is above.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Re: Check if a point is outside a curve

    Quote Originally Posted by MrShorty View Post
    After performing the regression for each pair of points, you will have a lookup table that can be used to find the regressrion equation that you will use for the region between each tabulated point. I might set this up as:

    1) C:D =LINEST(LN(B2:B3),LN(A2:A3)) (note array entry here) This will return a curve that connects the first two data points.
    2) copy this down until the 2nd to last entry in the table (note the use of relative references so that each regression is for one pair of points). The last entry in the table will error because there is not a point below it.
    3) At this point, you will use lookup function(s) based on your x0,y0 point to pull the parameters for the segment of the curve that you are interested in.

    I've attached my best attempt at an example. This isn't the only way to do it, but it illustrates how it could be done.

    I've included another point at 150 to illustrate what I mean be uncertainty. Assuming the algorithm is exact, the computer will say that 150 and 1.99e-6 is above. But, as I look at it, it is pretty close to the curve, to the point that I would wonder if we can really state that it is above.
    Waheyy!
    I actually tried something similar to this - I found linear equations to predict values between each pair of points. To say the least, it wan absolutely awful fit. LINEST() has me trumped it seems.
    I tested a few points and LINEST() predicts it more or less perfectly - as for the issue
    I've included another point at 150 to illustrate what I mean be uncertainty. Assuming the algorithm is exact, the computer will say that 150 and 1.99e-6 is above. But, as I look at it, it is pretty close to the curve, to the point that I would wonder if we can really state that it is above
    this situation won't be entirely devoid of human input. There will always be the option to judge it and decide manually.

    Thanks very much!

+ 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. Check for xx-yy point inside/outside graphic area
    By Blue_Wings in forum Excel General
    Replies: 20
    Last Post: 03-31-2012, 11:04 AM
  2. Distance from a point to a curve
    By bjack205 in forum Excel General
    Replies: 14
    Last Post: 02-04-2011, 02:10 PM
  3. Check if the number have two decimal point
    By darkhangelsk in forum Excel General
    Replies: 3
    Last Post: 08-20-2009, 01:14 PM
  4. Check Point in VBA
    By Brian Matlack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2006, 11:59 AM

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