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!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104
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
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
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
Joined
Feb 14, 2002
Messages
85,178
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
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
Joined
Feb 24, 2003
Messages
104
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
Joined
Dec 26, 2013
Messages
201
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
Joined
Feb 24, 2003
Messages
104
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,484
Messages
5,468,880
Members
406,618
Latest member
SAMEERS

This Week's Hot Topics

Top