=max(.. but don't include any number above 699

[still learning]

Hi
How would i write a max formula to look up the highest number below 699 ?
=max( A6:A300)

mike

 

[Joe4]

Just nest it in a MIN formula, i.e.

=MIN(MAX(A6:A300),699)

 

[Jeffrey Mahoney]

=MAXIFS(A6:A300,A6:A300,"<"&699)

 

[Joe4]

Note:

I just had a thought that my solution may not do what you want.
If you had 3 values, like 800, 600, and 400, it would return 699, not 600.
If you want 600 in that case, go with Jeffrey's solution.

 

[still learning]

Thanks Jeff, that's what I needed.
Hi Joe4. I wanted to exclude any number over 699

mike

 

[Joe4]

Thanks Jeff, that's what I needed.
Hi Joe4. I wanted to exclude any number over 699

Yeah, I realized that is probably what you were after I posted my reply.
My reply is how you would "cap" it at 699.