Determine MAX below a threshold

[AusSteelMan]

Hi everyone,

Quick one I just can't get right

I am after a formula that will return the MAX value below a threshold.

I have a list of numbers between 0 and 19000

I would like to determine the value that is the highest below 16000.

I guess the second thing is a conditional format that will highlight the cell that meets this criteria, but I think I could get this if I can get the formula right to determine the value.

I know it seems a simple request compared to some of the things I have asked for help with before, so I am just guessing that my brain must be in low gear at the moment

Thanks in advance
ASM

 

[Jonmo1]

It's an array formula entered with CTRL + SHIFT + ENTER

=MAX(IF(A1:A100<16000,A1:A100))

This must be entered with CTRL + SHIFT + ENTER

 

[AusSteelMan]

Thanks jonmo1

I had a CSE similar but apparently just screwed it up.

This worked great.

Thanks,
ASM