+ Reply to Thread
Results 1 to 7 of 7

next higher number row

Hybrid View

  1. #1
    Registered User
    Join Date
    01-20-2010
    Location
    bangladesh
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: next higher number row

    But can u give me explanation plz I will be glad to u

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: next higher number row

    =IFERROR(1/(1/MIN(IF(A1:A14>B1,A1:A14))),"")

    If there are no numbers greater than the lookup value then the MIN function will return 0.

    You don't want a result of 0 so I intentionally wrote the formula so that it will cause an error to be generated if there are no numbers greater than the lookup value. But, we then use the IFERROR function to trap that error.

    We can say that 1 divided by 1 divided by some number will equal that number UNLESS that number is 0. When the number is 0 the division operations will result in the #DIV/0! error.

    For example:

    If the result of:

    MIN(IF(A1:A14>B1,A1:A14)) = 3.25

    Then:

    1/(1/MIN(IF(A1:A14>B1,A1:A14))) = 3.25

    Then:

    =IFERROR(3.25,"") = 3.25

    However, if there are no numbers greater than the lookup value the result of:

    MIN(IF(A1:A14>B1,A1:A14)) = 0

    Then:

    1/(1/MIN(IF(A1:A14>B1,A1:A14))) = #DIV/0!

    Then:

    =IFERROR(#DIV/0!,"") = "" (blank)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Replies: 13
    Last Post: 08-13-2013, 12:51 PM
  2. Replies: 7
    Last Post: 03-22-2010, 05:22 PM
  3. get first higher number
    By Coaster in forum Excel General
    Replies: 9
    Last Post: 04-08-2009, 01:55 AM
  4. second higher number
    By HR157 in forum Excel General
    Replies: 1
    Last Post: 01-26-2007, 04:29 PM
  5. [SOLVED] comparing a value in a cell to see if it is higher than a number
    By PK in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-05-2005, 11:05 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