Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2005

Tip: Looking for answers? Try searching our database.

Maximum and Minimum with Complex Criteria

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
manoshde@gmail.com - 16 Dec 2005 16:13 GMT
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
Bob Phillips - 16 Dec 2005 16:21 GMT
A few assumptions made, but try

=MAX(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10))

as an array formula, so commit with Ctrl-Shift-Enter

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hello
>
[quoted text clipped - 27 lines]
>
> Manosh
Manosh - 16 Dec 2005 17:39 GMT
What would the formula for minimum be?
I tried to replace Max wih min but it doesnt work.
Would appreciate an answer on this,
regards
m
Bob Phillips - 16 Dec 2005 17:45 GMT
It worked for me.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> What would the formula for minimum be?
> I tried to replace Max wih min but it doesnt work.
> Would appreciate an answer on this,
> regards
> m
Bernie Deitrick - 16 Dec 2005 17:46 GMT
=MIN(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10,MAX(C1:C10)))

Also entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

> What would the formula for minimum be?
> I tried to replace Max wih min but it doesnt work.
> Would appreciate an answer on this,
> regards
> m
Harlan Grove - 19 Dec 2005 00:27 GMT
"Bernie Deitrick" <deitbe @ consumer dot org> wrote...
>=MIN(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10,MAX(C1:C10)))
>
>Also entered using Ctrl-Shift-Enter.
...

Or

=-MAX(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),-C1:C10))
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.