Finding the max of a column where values less than a specified number

M

Mark Silka

I have a column with values

1
2
3
4
5
6
7
8
9
10

I want to find the max number which is less than 6.
How do I accomplish this with an excel formula?

I tried doing MAX(If(A1:A10<6,A1:A10,0)
But that did not work
 
P

Paul Corrado

That works as an array formula. Hold down CNTRL+SHIFT+ENTER when you enter
the formula.

=MAX(IF(A1:A10<6,A1:A10))
 
Top