Try these...shorter, fewer function calls, normally entered.

For the MAX:

=LOOKUP(2,1/COUNTIF(A1:A5,G1:G10),G1:G10)

For the MIN:

=INDEX(G1:G10,MATCH(TRUE,INDEX(COUNTIF(A1:A5,G1:G1 0)0,,1),0))

Whe

A1:A5 = drop down lists
G1:G10 = items listed from lowest value to highest value

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I have a bunch of cells with a drop-down list

Are the cells with the drop downs in a contiguous range? Like A1:A5.

where someone has chosen a value (all non-blank cells)

Will there be any empty cells?


--
Biff
Microsoft Excel MVP


"Joe Lewis" wrote in message
...
Suppose I have a bunch of cells with a drop-down list allow users to
choose
between the folowing values:

pppp
ppp
pp
p
mp
mf
f
ff
fff
ffff

How do I write a function that will look through all the cells where
someone
has chosen a value (all non-blank cells) and pull out the max and min
value
where the "max" and "min" are based on the custom list above (i.e. "pppp"
is
the smallest value possible and "ffff" is the largest value possible)?

I would like for Excel to do this automatically for the user, and update
itself as the data might change.

Is this possible?

Thanks for any information you can provide.