Excel 2007 : Finding the first value to meet my criteria

[bmercer54]

I have a range of numbers of that decrease from highest to lowest. I need to find the first number that is smaller than my reference number.

[Ron Coderre]

With
A1:A10 containing numbers in descending order
and
B1 containing a reference number

This formula returns the largest value int A1:A10 that is smaller that the B1 value.

Please Login or Register  to view this content.

Is that something you can work with?

[bmercer54]

This helps in most cases, but I do have some instances where some of the numbers in my array are equal to the reference number. The formula you submitted returns the first number that is less than or equal to the reference number. Any suggestions on how to fix this? Thanks.

[Ron Coderre]

I'm not seeing that...

Example:
A1:A10 contains these values in descending order:

Please Login or Register  to view this content.

B1: (a reference value)

Formula that returns the first value that is SMALLER than the B1 value

Please Login or Register  to view this content.

When...

Please Login or Register  to view this content.

Are you seeing something different?

[DonkeyOte]

below not warranted unless there is duplicity of numbers...

=LARGE(A1:A10,1+COUNTIF(A1:A10,">="&B1))

[bmercer54]

I do have duplicate numbers but I was able to get both formulas to work. I got the INDEX formula to work by subtracting 0.001 from my reference number (B1-0.001). The LARGE formula worked as shown. Thanks for the help.