MIN with IF in array

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

I have a SS with student standardized test results for
different classrooms listed together. I want to check the
range c3:c43 against a60 to separate out only those scores
for that given room and find the MIN score. The problem
comes if there is an empty cell (no score) for a student
in the room; I get a zero returned as the lowest score. I
have tried the following functions entered as an arrays.
Any ideas?
=MIN(IF($C$3:$C$43=$A$60,(($u$3:$u$43))))
=MIN(IF(AND($C$3:$C$43=$A$60,$U$3:$U$43<>""),
(($U$3:$U$43))))
 
One way

=MIN(IF(($C$3:$C$43=$A$60)*($U$3:$U$43<>""),$U$3:$U$43))


entered with ctrl + shift & enter
 
I still get a O return as the MIN
-----Original Message-----
One way

=MIN(IF(($C$3:$C$43=$A$60)*($U$3:$U$43<>""),$U$3:$U$43))


entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom




.
Click to expand...
 
Maybe you have a real zero there? I know the formula works..
If you want to disregard zeros use

=MIN(IF(($C$3:$C$43=$A$60)*($U$3:$U$43<>0),$U$3:$U$43))

also note that you have to array enter it
 
Thanks so much; that worked. Apparently the said:
-----Original Message-----
Maybe you have a real zero there? I know the formula works..
If you want to disregard zeros use

=MIN(IF(($C$3:$C$43=$A$60)*($U$3:$U$43<>0),$U$3:$U$43))

also note that you have to array enter it

--

Regards,

Peo Sjoblom




.
Click to expand...
 
By the way, I used your same trick with the question I had
about the STDEV with the IF producing erratic answers due
to counting the empty data cell as a zero and it worked
there as well. I just changed MIN to STDEV! Thanks again
for all your help, Peo.
 
Back
Top