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

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

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

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



"Mark Silka" wrote in message
...
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