Maximums with criteria
Posted by Joel on October 05, 2000 12:55 PM
I am trying to find a function that will allow me to find a maximum that is less than 10000. The column that I need to search has 10000 in it but I don't want that to be my maximum, I want the highest number other than 10000 to be the maximum.
Have any clues or solutions???
Posted by Celia on October 06, 2000 3:02 AM
Joel
Array formula(Ctrl+Shift+Enter) :-
=MAX(IF($A$1:$A$10<10000,$A$1:$A$10,""),IF($A$1:$A$10<10000,$A$1:$A$10,"")
Celia
Posted by Celia on October 06, 2000 3:05 AM
Correction
Missed off a bracket. Should be :-
=MAX(IF($A$1:$A$10<10000,$A$1:$A$10,""),IF($A$1:$A$10<10000,$A$1:$A$10,""))
Celia
Posted by Celia on October 06, 2000 3:16 AM
Re: Correction
Should be, of course :-
=MAX(IF($A$1:$A$10<10000,$A$1:$A$10,""))
Which is the same as the answer already provided by Tim Francis-Wright which I have just noticed.
Celia
