find last value before limit in unsorted list

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top