find last value before limit in unsorted list

[hmmm...]

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!

 

[Rick Rothstein]

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!

No, those #N/A's change the problem completely from the one you presented to us early. More than likely, Aladin will pop in with a solution before I do, but I'll be thinking about it. For future questions you may ask... do not simplify your data or the question itself for us, all that will get you (as in the present case) is great answers for a question you do not actually have and which you do not really care about.

 

[Rick Rothstein]

No, those #N/A's change the problem completely from the one you presented to us early. More than likely, Aladin will pop in with a solution before I do, but I'll be thinking about it. For future questions you may ask... do not simplify your data or the question itself for us, all that will get you (as in the present case) is great answers for a question you do not actually have and which you do not really care about.

Okay, I think I have a solution. Give this array-entered** formula a try...

=INDEX(A:A,MAX(IF(ROW(A1:A18)< MIN(IF(NOT(ISERROR(A1:A18)),IF(A1:A18>=500,ROW(A1:A18)))),IF(NOT(ISERROR(A1:A18)),ROW(A1:A18)))))<min(if(not(iserror(a1:a18)),if(a1:a18><min(if(not(iserror(a1:a<font color="#FF0000">

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

I am still betting on Aladin (or even one of the other volunteers) coming in with a better formula, but until then, the above should work for you (but come back to this thread every now and then to see if a better formula has been posted).</min(if(not(iserror(a1:a</min(if(not(iserror(a1:a18)),if(a1:a18>

 

[hmmm...]

Sorry to have led you two astray by leaving out the details--I was trying to make the problem simpler to pose, but I can see I shouldn't have done that!

Rick, your solution works, and I can even understand that one: you are looking for the maximum row number that both (is less than the row containing the first <=500 value) and also (does not contain an error code). Nice!

Thank you both for your solutions and I will pick the first two apart so I know how they work.

 

[Aladin Akyurek]

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!

Assumptions:

(1) when the data contains the threshold value, return that value.
(2) when no value >= threshold, return the value closest to threshold.
(3) otherwise return the last value that occurs before the first value > threshold

The earlier formula obeys the above specification in a range which is not infested with #N/A's. What follows is meant to replicate the earlier fomula for a range that also house #N/A's. Rick's proposals differ I believe w.r.t. the first two points.

Define BigNum as referring to:

=9.99999999999999E+307

Control+shift+enter, not just enter:

=IFERROR(LOOKUP(BigNum,A1:INDEX(A1:A18,MATCH(FALSE,IF(ISNUMBER(A1:A18),
  IF(A1:A18<=C1,ROW(A1:A18)-ROW(A1)+1),#N/A),0)-1)),
  INDEX(A1:A18,MATCH(MIN(ABS(IF(ISNUMBER(A1:A18),A1:A18,BigNum)-C1)),
  ABS(A1:A18-C1),0)))

 

[Aladin Akyurek]

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.

No need to jump to such a conclusion. A formula with ROW(A1) is risky. Try to add/insert rows in front of the formula with that ROW bit and observe the results.