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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityFrom 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?
Use array formula:
{=MAX(IF(A1:A4<C1, A1:A4, 0))}
I'm surprised that the solution is actually so trivial!
=LOOKUP(C1;A1:A4)
{=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
Sep 5 '13 at 12:19
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)))}