+ Reply to Thread
Results 1 to 3 of 3

Wants to calculate and show intersecting point on Graph

  1. #1
    Registered User
    Join Date
    01-31-2012
    Location
    mumbai
    MS-Off Ver
    Excel 2007
    Posts
    24

    Wants to calculate and show intersecting point on Graph

    i have a Graph on Admin bldg sheet. Where I have calculated Required Fixture units.
    i want to draw a vertical line from x axis at Required calculated (Currently 335)
    Then i want to draw a horizontal line from the point of intersect of vertical line and Demand (Load), LPM System With Flush Valve (red line), to know the point of Y axis.
    Please help
    Attached Files Attached Files

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

    Re: Wants to calculate and show intersecting point on Graph

    How much is a charting question and how much is a spreadsheet question? In order to show a vertical line connecting (x,0) with (x,f(x)), one first needs to calculate y=f(x) in the spreadsheet (the chart cannot do this for us).

    If this is exclusively a charting question and you have already calculated y=f(x) (maybe this is the value F25??), then adding the lines to the chart should be as easy as adding another data series. In a convenient place, enter the data for the new series. Perhaps in E27 enter =$E$25, copy into E28, and enter 0 into E29. In F27, enter 0, In F28 enter =$F$25 and copy ing F29. Then add a new data series using E27:E29 as the X values and F27:F29 as the Y values.

    If the larger question is how to calculate f(x), I'm not sure how much we can help without knowing something about the industry you are working in and what are considered the best practices for this sort of thing. In a generic sense, there are usually two strategies for this kind of thing -- regression and interpolation. Since I doubt any of us knows the best practices for your industry, I don't think any of us can decide on the best strategy for this. If you need help deciding how to calculate f(x), we need you to help us understand the process/algorithm for performing the calculation. Then we should be able to help you program that calculation into the spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    156

    Re: Wants to calculate and show intersecting point on Graph

    HI

    finding the function is not so easy, charting problem is much easier.

    my guess is that F25 is showing the match from the Sheet "Sewage Data (Do not Change)". Issue is that you look for the value in E25 (= 335) and you get back the value of 398 (which is actually the fit for 400 units)

    So what I did is use the let function to write a formula to find the an approximate match.
    e.g.
    x y
    300 322
    400 398
    you search for x = 335 and my formula return 349 assuming linear function between the points.
    Let in excel help to make a formula more readable.

    __x define the range in the data for x values
    __search the x value you look for
    __yOffset the column with the Y values as offset to the x value Column

    HTML Code: 
    If you cant use let (no O365), then you can just go backwards with the formula and make one big one and replace the variable with the formula itself


    I guess this should be close enough as you don't build a rocket :-)
    Attached Files Attached Files

+ 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. Graph data is on wrong reference point on graph.
    By NThompson7* in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-02-2015, 08:40 AM
  2. [SOLVED] Creating a line graph over a bar graph to show actual v.s. goal?
    By nscarritt in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-03-2014, 03:09 PM
  3. Intersecting value of graph
    By sac0011 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 01-15-2014, 01:04 AM
  4. intersecting value of graph
    By sac0011 in forum Excel General
    Replies: 1
    Last Post: 01-04-2014, 12:16 PM
  5. Making last known data point show as value on graph?
    By Daniel86 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-21-2013, 07:42 AM
  6. Graph Each Data Point Value Show Up
    By batmanfan in forum Excel General
    Replies: 5
    Last Post: 05-08-2010, 07:54 PM

Tags for this Thread

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