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

1. ## 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!  Register To Reply

2. ## 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  Register To Reply

3. ## 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.  Register To Reply

4. ## 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?  Register To Reply

5. ## 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:

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!   Register To Reply

##### Users Browsing this Thread

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

#### 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