min function from different columns
 

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

Hello,

Maybe you have leading/trailing spaces? ...

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

--
Regards,
Zack Barresse, aka firefytr

It
"sonofroy" wrote in message
...
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

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

"sonofroy" wrote:

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

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

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

--
Regards,
Zack Barresse, aka firefytr

"Ron Coderre" wrote in message
...
See if this works for you:
=MIN(IF(($A$1:$A$6="x"),$B$1:$B$6))
--
Regards,
Ron

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

--
Regards,
Ron

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!!!!

"Peo Sjoblom" wrote:

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

"sonofroy" wrote:

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

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.

--
Regards,
Zack Barresse, aka firefytr

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

--
Regards,
Ron

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?

--
HTH

RP

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

--
Regards,
Ron