find last value before limit in unsorted list

hmmm...

Board Regular
Hi,

Let's say these cells contain these values:

cell a1=212
cell a2=324
cell a3=488
cell a4=538
cell a5=498
cell a6=444


I have a "threshold" value of 500.

I would like Excel to return the highest value that occurs before (in the array order) the threshold. (In this case the value would be 488). I need to keep the list unsorted. It would be best if I could find a formula (rather than VBA solution) for the sheet that I am using.

Any ideas? Thank you!
 

hmmm...

Board Regular
Actually, I am looking for the last value (in the array order) that occurs before (in the array order) the threshold value. My phrase "highest value" isn't necessay.
 

Rick Rothstein

MrExcel MVP
Actually, I am looking for the last value (in the array order) that occurs before (in the array order) the threshold value. My phrase "highest value" isn't necessay.
I think this formula will do what you want...

=INDEX(A:A,SUMPRODUCT(1*(A1:A6<500),1*(A2:A7>=500),ROW(A1:A6)))
 

Aladin Akyurek

MrExcel MVP
Looks like:
Rich (BB code):
=IFERROR(INDEX(A1:A6,MATCH(FALSE,IF(A1:A6<=C1,ROW(A1:A6)-ROW(A1)+1),0)-1),
  INDEX(A1:A6,MATCH(MIN(ABS(A1:A6-C1)),ABS(A1:A6-C1),0)))
which you need to confirm with control+shift+enter, not just enter.
 

Rick Rothstein

MrExcel MVP
I think this formula will do what you want...

=INDEX(A:A,SUMPRODUCT(1*(A1:A6<500),1*(A2:A7>=500),ROW(A1:A6)))
Forget the above formula.... it really does not work well. On the other hand, I believe this array-entered formula does work...

=INDEX(A:A,MIN(IF((A1:A8<500)*(A2:A9>=500),ROW(A1:A8))))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Note the second (middle) range is offset from the other two by one row... that is required to make the code work. So, when you adjust the code for your actual ranges, make sure that offset is preserved.
 

hmmm...

Board Regular
Aladin, Thank you for your reply. I expected I'd be able to decipher a reply better than I can yours. Your formula works for the example I gave, and I'll dissect it until I understand it, but that might take a while. Meanwhile, my data actually look like this:

cell a1=#N/A
cell a2=#N/A
cell a3=212
cell a4=#N/A
cell a5=#N/A
cell a6=#N/A
cell a8=324
cell a9=#N/A
cell a10=488
cell a11=#N/A
cell a12=#N/A
cell a13=538
cell a14=#N/A
cell a15=498
cell a16=#N/A
cell a18=#N/A
cell a19=444
cell a20=#N/A

I thought I'd be able to plug IFNA=FALSE into any formula in a reply, but apparently I should have put in this detail, since I can't see yet where I would plug this into your formula. Thanks!
 

pleeseemailme

Board Regular
Even if this would constitute a solution to the question under question (I doubt it), the suggested formula in the post you link to is not robust. This board also contains many kindered formulas without such flaw.
Aladin,
I'm not sure what I did to ensue such hostility...please clear this up for me so I don't make such a mistake again. I also don't understand what 'robust' means in the context above or to which flaw you refer in the formula. I do like the 'question under question' phrase. Let me know.
 

hmmm...

Board Regular
Rick, thank you for your reply too. Yours works on my first example too, of course; and again I'm surprised that I don't see yet how it does. Can it work with the #NAs in there? So far I can't figure that out. Thanks!
 

Some videos you may like

This Week's Hot Topics

Top