W
Weissme
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
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