+ Reply to Thread
Results 1 to 6 of 6

Searching for intermediate value

  1. #1
    Registered User
    Join Date
    05-29-2008
    Location
    Ontario, Canada
    Posts
    19

    Searching for intermediate value

    Let's say I have two columns of data like this:

    x y
    147 46.8
    148 48.2
    149 49.6
    150 50.4
    151 51.2
    152 52.6

    I want to find what x value corresponds to a value of y = 50. For example in this data, at y = 50, x =149.5. So essentially I have to search for an interpolated value that isn't actually in the data.

    I've tried getting the equation of a line using SLOPE() and INTERCEPT() and then solving for x, but this has given me inaccurate results.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,436
    See attached for formula
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    05-29-2008
    Location
    Ontario, Canada
    Posts
    19
    Thanks Andy. It works great, however some sections have data similar to this, but in descending order. Is there an easy way to sort the list in ascending order or use vlookup for a descending list?

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,436
    If you select the data table you can use menu Data > Sort.

    Or this revision using an array formula.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-29-2008
    Location
    Ontario, Canada
    Posts
    19
    I tried using that formula but it just returns the first value in the list. However it doesn't have those curly {} brackets around it. When I add those, Excel doesn't treat it as a formula. I have too many different columns to search in order to sort them all using the menu.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,436
    The curly brackets denote the formula is an array formula and needs to be commited with CTRL+SHIFT+ENTER rather than just ENTER.

    The brackets will be added automatically by excel and disappear when you edit the formula.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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