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.

>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" <(E-Mail Removed)> wrote in message
news:93A07190-1D21-4F0F-89D8-(E-Mail Removed)...
> 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.

Hi,

I don't understand how the title "automatic sorting" fits into the problems
below??

However, this may be a start, assuming the users are entering info in column
A and you custom list is in G1:G10 then the

Max:
=INDEX(G1:G10,MAX(IF(ISNUMBER(MATCH(A1:A9,G1:G10,0)),MATCH(A1:A9,G1:G10,0))),0)
Min:
=INDEX(G1:G10,MIN(IF(ISNUMBER(MATCH(A1:A9,G1:G10,0)),MATCH(A1:A9,G1:G10,0))),0)

These are both array formulas, so you must press Shift+Ctrl+Enter to enter
them.

if this helps, please click the Yes button

Cheers,
Shane Devenshire

"Joe Lewis" wrote:

> 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.

Thank you so much. That worked perfectly!

"Shane Devenshire" wrote:

> Hi,
>
> I don't understand how the title "automatic sorting" fits into the problems
> below??
>
> However, this may be a start, assuming the users are entering info in column
> A and you custom list is in G1:G10 then the
>
> Max:
> =INDEX(G1:G10,MAX(IF(ISNUMBER(MATCH(A1:A9,G1:G10,0)),MATCH(A1:A9,G1:G10,0))),0)
> Min:
> =INDEX(G1:G10,MIN(IF(ISNUMBER(MATCH(A1:A9,G1:G10,0)),MATCH(A1:A9,G1:G10,0))),0)
>
> These are both array formulas, so you must press Shift+Ctrl+Enter to enter
> them.
>
> if this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Joe Lewis" wrote:
>
> > 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.

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:G10)>0,,1),0))

Where:

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

--
Biff
Microsoft Excel MVP


"T. Valko" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> news:93A07190-1D21-4F0F-89D8-(E-Mail Removed)...
>> 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.
>
>