Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find minimum of range based on multiple criteria
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$491)),$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 -- Weissme ------------------------------------------------------------------------ Weissme's Profile: http://www.excelforum.com/member.php...o&userid=34044 View this thread: http://www.excelforum.com/showthread...hreadid=544071 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find minimum of range based on multiple criteria
=MIN(IF(($H$6:$K$49=2001)*($K$6:$K$49=1),$F$6:$F$4 9))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Weissme" wrote in message ... 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$491)),$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 -- Weissme ------------------------------------------------------------------------ Weissme's Profile: http://www.excelforum.com/member.php...o&userid=34044 View this thread: http://www.excelforum.com/showthread...hreadid=544071 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find minimum of range based on multiple criteria
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! -- Weissme ------------------------------------------------------------------------ Weissme's Profile: http://www.excelforum.com/member.php...o&userid=34044 View this thread: http://www.excelforum.com/showthread...hreadid=544071 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find minimum of range based on multiple criteria
Mine has one les IF <vbg
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "Weissme" wrote in message ... 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! -- Weissme ------------------------------------------------------------------------ Weissme's Profile: http://www.excelforum.com/member.php...o&userid=34044 View this thread: http://www.excelforum.com/showthread...hreadid=544071 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting based on multiple criteria | Excel Discussion (Misc queries) | |||
Show top five records based on meeting multiple criteria | Excel Worksheet Functions | |||
MIN within range based on criteria | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
Count rows based on multiple criteria | Excel Worksheet Functions |