Problem with MIN

[Joey]

I have a problem with using MIN. What I'm trying to do
is load up some data into excel and find the minimum
value of all things that have a specific name and action.
I won't know how many rows are going to be loaded so I
just set it to the max 65536.

For example, Column A contains all the names, Column B
contains all the actions, and Column C has their value.
So I'm trying to find the min value of all the values
that has the name "Sam" and its current action
is "Defend". For some reason, min returns 0. I was told
that it treats blank cells as zero.

I've tried to find the MAX and it worked fine:

{=MAX(($A$2:$A$65536="SAM")*($D$2:$D$65536="Defend")*
($E$2:$E$65536)}

 

[Peo Sjoblom]

First of all you need to add an IF for MIN, secondly you need to dodge blank
cells
where the conditions are TRUE in the other columns

=MIN(IF(($A$2:$A$65536="SAM")*($D$2:$D$65536="Defend")*(E$2:$E$65536<>""),$E
$2:$E$65536))

should work, having said that you are probably better not using all the
rows, are you really going to use up
that much? It will slow the spreadsheet just by having that many cells, why
not using 10000 cells to start with?
If it gets too slow you should probably combine a few formulas and a help
column, you could use

=IF(AND(A2="Sam",D2="Defend"),E2,"")

in F2

copy down then use a non array MIN/MAX

=MIN(F2:F10000)

normal MIN will disregard blanks

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)