Excel: searching for a maximum lower than X

From the sorted list of values, I'm looking for a maximum that is lower than X

Example:

  A  B  C
1 1     X=7
2 3
3 5
4 8   ANS=5

Do you have any smart ideas for this problem?

Karusmeister

Posted 2013-09-05T11:52:36.850

Reputation: 209

Yes, you can use VBa to do this, but not sure if VBa is something you want since you specified worksheet-function? The concept is, take all numbers into an array, sort ascending, filter for results only below the number and take the last item from the array. – Dave – 2013-09-05T11:56:14.173

1I'd prefer not to use VBA – Karusmeister – 2013-09-05T11:57:17.477

Answers

Use array formula:

{=MAX(IF(A1:A4<C1, A1:A4, 0))}

jon

Posted 2013-09-05T11:52:36.850

Reputation: 604

1Of course this assumes that C1 is non-negative. – Scott – 2013-09-06T19:12:43.733

@Scott, Good point, do you have anything that will work for negative numbers as well? – jon – 2013-09-09T09:33:26.530

I'm surprised that the solution is actually so trivial!

=LOOKUP(C1;A1:A4)

Karusmeister

Posted 2013-09-05T11:52:36.850

Reputation: 209

hmmm.... got a vote down - is there something wrong with my answer? – Karusmeister – 2013-09-05T12:13:39.320

Maybe because you asked for "max but less than", and lookup will give you "max less than or equal". You should use array formula {=MAX(IF(A1:A4<C1, A1:A4, 0))}. Use ctrl+shift+enter to tell excel you've entered an array formula. ps, down vote wasn't me. – jon – 2013-09-05T12:19:52.603

@Jon good remark. Could you please post your comment as an answer and I'll accept it – Karusmeister – 2013-09-05T12:40:38.313

@DaveRook never mind :) – Karusmeister – 2013-09-05T12:40:56.580

I believe the following will work for positive or negative values of C1.

{=IF(C1>=0,MAX(IF((A1:A4)<C1,A1:A4)),MAX(IF(-1*(A1:A4)>ABS(C1),A1:A4)))}

Mike M

Posted 2013-09-05T11:52:36.850

Reputation: 1