Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
See if this works for you:
=MIN(IF(($A$1:$A$6="x"),$B$1:$B$6)) -- Regards, Ron |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
That is . . .
=MIN(IF(A1:A6="x",B1:B6))works for me with and without CSE -- Regards, Ron |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
analysing data from alternate columns using the countif function | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Sumif function with two criteria from different columns | Excel Worksheet Functions | |||
MATCH function - 2 columns w/ SIMILAR, not EXACT data | Excel Worksheet Functions | |||
How do I perform a "Countif" function for Two Columns? | Excel Worksheet Functions |