Maximums with criteria
Posted by Joel on October 05, 2000 12:56 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 Ben O. on October 05, 2000 1:14 PM
There's probably a better way of doing it, but I would add a column (let's call it column B) next to the column with your values (column A) and paste a formula like =IF(A1<10000,A1,"") all the way down, so that column B only contains the values in column A that are less than 10,000. =MAX(B:B) will give you the maximum value in column B.
-Ben
Posted by Tim Francis-Wright on October 05, 2000 1:45 PM
A better way to do this for a range is to
use the control-shift-enter formula
=MAX(IF(List<10000,List,FALSE))
where List is the range in question.
(Be sure to hit control-shift-enter in lieu of enter.)
HTH There's probably a better way of doing it, but I would add a column (let's call it column B) next to the column with your values (column A) and paste a formula like =IF(A1<10000,A1,"") all the way down, so that column B only contains the values in column A that are less than 10,000. =MAX(B:B) will give you the maximum value in column B. -Ben :
