min function from different columns

[Guest]

hi! I need some help again! I have a sheet setup like this.
a b
1 x 41
2 y 30
3 x 45
4 y 43
5 x 37
6 y 29

I am using the function
=MIN(IF(a1:a6="x",b1:b6)) then C+S+E

this keeps returning zero
How can I correct this formula to actually return the minimum for x instead
of zero

Thanks in advance.
Derek

 

[Zack Barresse]

Hello,

Maybe you have leading/trailing spaces? ...

=MIN(IF(TRIM(a1:a5)="x",b1:b6))

 

[Guest]

Did you enter the formula with ctrl + shift & enter? Do you have any blank
cells in the B range? What do you mean with "Then C+S+E" If you mean that you
would have one formula with non-contiginous ranges with criteria it won't
work. If you have blanks you can change to

=MIN(IF((A1:A6="x")*(B1:B6<>""),B1:B6))



Regards,

Peo Sjoblom

 

[Guest]

See if this works for you:
=MIN(IF(($A$1:$A$6="x"),$B$1:$B$6))

 

[Zack Barresse]

Shouldn't make a difference with the parenthasis. It's already contained
within the correct syntax..

 

[Guest]

That is . . .
=MIN(IF(A1:A6="x",B1:B6))works for me with and without CSE

 

[Guest]

C+S+E = Ctrl+shift+enter. Yes there are zeros in the b range your formula
work i did not jnow the )*(

Thanks a bunch!!!!

 

[Zack Barresse]

Which means you are only looking at one cell - the upper most cell in the
range, in this case, A1. Use the Formula Auditor to step through your
formula and see how it's not actually performing without being entered with
Ctrl + Shift + Enter.

 

[Bob Phillips]

Try putting a value of not x in A1:A6 with a value in B smaller than all the
values associated with x. Still works?