+ Reply to Thread
Results 1 to 5 of 5

How to find value and address of first cell in a range that is smaller than X?

  1. #1
    Registered User
    Join Date
    03-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    61

    How to find value and address of first cell in a range that is smaller than X?

    Hi Excel Experts!

    I just can't seem to figure out formulas that look at a range of cells (say A1:A50) and find 1) the value of that cell, and 2) the cell address of the first cell that meets Condition X (in this case, is equal or smaller than A1-Y, where Y is a constant).

    The final result would be three columns. Column A = data. Column B = value of the first cell within the next 50 cells that is equal or less than X. Column B = address of the first cell within the next 50 cells that is equal or less than X.

    I have a large data set, so there are tens of thousands of rows than need to be populated with these formulas!

    I've been struggling with INDEX and MAX and COUNTIF functions, but I just can't get things to work.

    Any ideas?

    Thanks!

    EDIT: added clarification about Condition X
    Last edited by hadamhiram; 03-26-2013 at 08:32 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to find value and address of first cell in a range that is smaller than X?

    Where is X specified?

    Do you mean that you want the addresses in column C?

    Pete

  3. #3
    Registered User
    Join Date
    03-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: How to find value and address of first cell in a range that is smaller than X?

    Ah, I should have been more clear!

    X = (A1-Y) , where A1 is the first value in the range, and Y is just a constant.

    The idea is to find the first cell in the range that drops Y units below the first value in the range.

  4. #4
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: How to find value and address of first cell in a range that is smaller than X?

    assuming that you had data in column A ranging from A1:A50 and your constant value of "Y" resides in cell D1, you could use the following formula D2 to get the first value that is lower than your "first value - Y":

    Please Login or Register  to view this content.
    and, the following formula D3 to get the location of that cell:

    Please Login or Register  to view this content.
    does that help?
    Last edited by icestationzbra; 03-26-2013 at 09:31 PM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  5. #5
    Registered User
    Join Date
    03-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: How to find value and address of first cell in a range that is smaller than X?

    Brilliant, thank you! That seems to work very well, and it also helps handle errors.

    I actually got something working myself, although without the ISERROR component:

    =ADDRESS(MATCH(TRUE,INDEX(A1:A50<=(A1-$F$1),0),0)+0,1,4)

    where A1:A50 is the range, and (A1-$F$1)=X, meaning that $F$1 is the location of my constant Y.

    That formula goes in B1, and so I then just use =INDIRECT(B1) to obtain the value itself!


    Last edited by hadamhiram; 03-26-2013 at 10:04 PM.

+ 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