+ Reply to Thread
Results 1 to 8 of 8

Help Fixing A Formula To Find The Next Greater Value

  1. #1
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Question Help Fixing A Formula To Find The Next Greater Value

    Hello,
    I need help fixing a formula. I receive the #REF! error and the wrong answers at times and would like some help fixing it. I need to find the next greater value of a value selected. The values reside in column C and the values to choose between for the next greater value reside in columns D:G and the formula itself is in column H.

    Example:
    A1: Date
    B2: Time
    C2: 0.9833 - Find next greater value.

    D2: 0.9831
    E2: 0.9823
    F2: 0.9873
    G2: 0.9865

    H2: #REF - #REF! Error

    I2: .9865 (Is what the answer should be in H.)


    I attached a spreadsheet that has other examples and the formula itself. Thanking you in advance for any and all help.
    Attached Files Attached Files
    Last edited by artiststevens; 06-23-2012 at 10:09 PM.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help Fixing A Formula To Find The Next Greater Value

    Mainly based on what you described, augmented by your posted formula, this regular formula returns the first D:G value larger than Col_C. If none, then it returns the largest D:G value
    Please Login or Register  to view this content.
    Note: not all of the calculated values match your "should be" column.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Re: Help Fixing A Formula To Find The Next Greater Value

    That works perfectly! Thank you very much Ron!

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Help Fixing A Formula To Find The Next Greater Value

    I have another answer. Try in H2 and pull down:

    Please Login or Register  to view this content.

    Now I'll need to study Ron's answer to see why it is an easier answer.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Help Fixing A Formula To Find The Next Greater Value

    I find there are differences between Ron's answer and mine. See attached...
    Attached Files Attached Files

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help Fixing A Formula To Find The Next Greater Value

    Quote Originally Posted by MarvinP View Post
    I find there are differences between Ron's answer and mine. See attached...
    Based on the OP's formula: If none of the D:G values is larger than the Col_C value...Use the largest D:G value.
    The formula you posted selects the smallest D:G value in that instance.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Help Fixing A Formula To Find The Next Greater Value

    I must have missed the part about using the largest. Thanks for the correction.

  8. #8
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Re: Help Fixing A Formula To Find The Next Greater Value

    Thanks for the additional help Marvin, but Ron's formula works fine.

+ 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