Hello

I hope i am not duplicating any previous query.

I am trying to generate a minimum and maximum summary for a list that
includes similar logical labels.

For example

If my list is

Community Park.....10ha
District Park...20ha
Canal..3ha
Open Space..4ha
Lake...1ha

I want to find the min/ max of all types of Green spaces (Community
Park, District Park and Open Space) and Water Bodies (Lake, Canal).

Is there an easy way to do this without adding another column?

For example one could add a col that identifies Community Park,
District Park and Open Space as Green Spaces and search the min/ max in
this but as my list is long and needs frequent updates it would be a
pain to maintain.

Thanks in advance.

Manosh

I'm a bit confused but if you had
aaa 1 bbb
bbb 2 bbb
ccc 3 ccc
you could use an array formula such as
=max(if(rngA="aaa"),rngb)
=MAX(IF(C1:C4="aaa",D1:D4))
array formulas must be entered using ctrl+shift+enter vs just enter
--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Hello

I hope i am not duplicating any previous query.

I am trying to generate a minimum and maximum summary for a list that
includes similar logical labels.

For example

If my list is

Community Park.....10ha
District Park...20ha
Canal..3ha
Open Space..4ha
Lake...1ha

I want to find the min/ max of all types of Green spaces (Community
Park, District Park and Open Space) and Water Bodies (Lake, Canal).

Is there an easy way to do this without adding another column?

For example one could add a col that identifies Community Park,
District Park and Open Space as Green Spaces and search the min/ max in
this but as my list is long and needs frequent updates it would be a
pain to maintain.

Thanks in advance.

Manosh

wrote:
Hello

I hope i am not duplicating any previous query.

I am trying to generate a minimum and maximum summary for a list that
includes similar logical labels.

For example

If my list is

Community Park.....10ha
District Park...20ha
Canal..3ha
Open Space..4ha
Lake...1ha

I want to find the min/ max of all types of Green spaces (Community
Park, District Park and Open Space) and Water Bodies (Lake, Canal).

Is there an easy way to do this without adding another column?

For example one could add a col that identifies Community Park,
District Park and Open Space as Green Spaces and search the min/ max in
this but as my list is long and needs frequent updates it would be a
pain to maintain.

Thanks in advance.

Manosh

------------------------

If your list of criteria "is long and needs frequent updates" then I would
certainly want to use helper columns rather than embedding all that complexity
into some difficult to read IF(..) statement a mile long which is particularly
difficult to update. Using lots of helper columns would (hopefully) allow you
to set it up to automatically determine lots of these choices by using table
lookups and such (i.e. a table headed "Green Space" that would list which
elements you want included). My goal, was I was you, would be to make it as
absolutely simple to update as possible rather than trying to avoid extra columns.

If you simply don't like the look of helper columns you can hide them or put
them on their own sheet in the back.

Good luck...

Bill

Thanks Don and Bill for your timely replies,

I particularly appreciate Bills recommendation of using a lookup table,
i agree that it would certainly be the way to go,

However here is a suggestion i received on another group and i thought
i should share it with you

=MIN(IF(ISNUMBER(FIND({"Canal","Lake"},C34:C201)), D34:D201))

Regards

Manosh

It's really NOT another group. You are actually multi-posting which is
frowned on here. Most of us read the different sub groups.

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Thanks Don and Bill for your timely replies,

I particularly appreciate Bills recommendation of using a lookup table,
i agree that it would certainly be the way to go,

However here is a suggestion i received on another group and i thought
i should share it with you

=MIN(IF(ISNUMBER(FIND({"Canal","Lake"},C34:C201)), D34:D201))

Regards

Manosh