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 / Worksheet Functions / May 2006

Tip: Looking for answers? Try searching our database.

find minimum of range based on multiple criteria

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Weissme - 21 May 2006 16:35 GMT
I have about 6 columns of data, but only 3 are important in what I'm
trying to find.  Here is an example of my spreadsheet:

Score   FY      Awarded
131     2001     1
145     2001     1
120    2001      0
256    2002      0
344    2002     1
138    2002     1
111    2002     0

Rows 5 through 49 have these data.

etc... up until FY 2006.  The data are assorted by FY ascending.  1 in
the Award column means it was awarded, 0 means it wasn't.  I want to
find a formula, if possible, that will find the minimum score that was
awarded in each FY.  I was able to do it with this:
=DMIN($F$5:$K$49,"Score",F1:K2) where the criteria are "=2001" or
whichever FY I am interested in and ">0" for the FY column.  But, this
is messy because I have to either set up several database headers, or
manually go in and change whichever FY I am searching for.  Clearly I
can do this all manually, but I want to find a formula where all I have
to do is change the FY in the formula to get my result.  I've also been
trying things like:

=MINA(IF((AND($H$6:$K$49=2001,$K$6:$K$49>1)),$F$6:$F$49))

Thoughts?  Is what I'm trying to do even possible?  Or should I just go
in and do it all manually...

Thanks in advance

Signature

Weissme

Bob Phillips - 21 May 2006 16:49 GMT
=MIN(IF(($H$6:$K$49=2001)*($K$6:$K$49=1),$F$6:$F$49))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> I have about 6 columns of data, but only 3 are important in what I'm
> trying to find.  Here is an example of my spreadsheet:
[quoted text clipped - 28 lines]
>
> Thanks in advance
Weissme - 21 May 2006 16:59 GMT
Hi, Thanks!  I will try that, but I also found that this works:

{=MIN(IF($K$6:$K$49=1,IF($H$6:$H$49=O10,$F$6:$F$49)))}

Most exciting triumph of my day I think.

Thanks again!

Signature

Weissme

Bob Phillips - 21 May 2006 17:21 GMT
Mine has one les IF <vbg>

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Hi, Thanks!  I will try that, but I also found that this works:
>
[quoted text clipped - 3 lines]
>
> Thanks again!
 
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.