Find lowest value in range excluding 0

[karini4]

I have been trying to enter a formula to find the lowest value in a range, but I want to exclude 0's. I haven't been able to figure it out. Any suggestions?

 

[JWahlin]

Hi,

Thanks, but it didn't quite work for me
First i cant have , in the formula (maybe some language diff) but i use ;
But it will only give me a #ref?/#name? error

Do I need to do something special due to that i'm looking for a text string?

this is how my formula looks like now

{=MIN(IF(BF50:BF510=CH53;X50:X510))}

BF is where the names are, CH53 is the name im looking for and X is where i look for the min value.

What could be wrong....


Kind regards

JW

 

[Aladin Akyurek]

Hi,

Thanks, but it didn't quite work for me
First i cant have , in the formula (maybe some language diff) but i use ;
But it will only give me a #ref?/#name? error

Do I need to do something special due to that i'm looking for a text string?

this is how my formula looks like now

{=MIN(IF(BF50:BF510=CH53;X50:X510))}

BF is where the names are, CH53 is the name im looking for and X is where i look for the min value.

What could be wrong....


Kind regards

JW

If CH53 is not a cell address...

{=MIN(IF(BF50:BF510="CH53";X50:X510))}

 

[JWahlin]

Hi,

No CH53 is the cell adress. This cell contains the name of the person.

 

[Aladin Akyurek]

Hi,

No CH53 is the cell adress. This cell contains the name of the person.

OK.

What is the result of:

=MIN(IF(BF50:BF510=CH53;X50:X510))

confirmed with control+shift+enter, not just with enter?

If the result is not what you expect, what is the result of

=COUNTIF(BF50:BF510,CH53)

which you confirm with just enter?

 

[JWahlin]

Hi,

I get the result 4, for the countif formula

which is the correct number of entries for this person

but for the min formula (shift+ctrl+enter) i get #NAME?

 

[Aladin Akyurek]

Hi,

I get the result 4, for the countif formula

which is the correct number of entries for this person

but for the min formula (shift+ctrl+enter) i get #NAME?

Right. What do you get with:

=COUNTIF(BF50:BF510,#NAME?)

=COUNTIF(X50:X510,#NAME?)

If one of these yields a non-zero result, try to post the formula that you have in the range giving this #NAME? error.

 

[JWahlin]

Hi again,

Problem solved, i entered the wrong language in the formula

Thanks for your help!!!

 

[MediChick]

I would like to make a formula to find the lowest value, excluding zero, but over multiple pages of the same cell. I think I'm close to the right formula, but am still getting errors.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
=MIN(IF(Sheet1:Sheet7!B2<>0,Sheet1:Sheet7!B2))
and
=MIN(IF(Sheet1:Sheet7!B2,Sheet1:Sheet7!B2))<o></o>
<o></o> <o></o>
I use ctrl+shift+enter to finish the formula for both of these attempts, which adds the {}, but still get #Ref Reference Error.
<o></o>
<o> </o>
I checked the sheets and they all have a number in the B2 Cell (or are blank) and ALL are formatted to be a number. I'd love a suggestion!
Thanks!!<o></o>

 

[T. Valko]

I would like to make a formula to find the lowest value, excluding zero, but over multiple pages of the same cell. I think I'm close to the right formula, but am still getting errors.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
=MIN(IF(Sheet1:Sheet7!B2<>0,Sheet1:Sheet7!B2))
and
=MIN(IF(Sheet1:Sheet7!B2,Sheet1:Sheet7!B2))<o></o>
<o></o><o></o>
I use ctrl+shift+enter to finish the formula for both of these attempts, which adds the {}, but still get #Ref Reference Error.
<o></o>
<o></o>
I checked the sheets and they all have a number in the B2 Cell (or are blank) and ALL are formatted to be a number. I'd love a suggestion!
Thanks!!<o></o>

If there won't be any negative numbers...

=SMALL(Sheet1:Sheet7!B2,FREQUENCY(Sheet1:Sheet7!B2,0)+1)

 

[sulakvea]

hi, here's a non-array formula for the hell of it (besides, online spreadsheets dont supports arrays, in case you wanna upload):

if there are no negatives:
=SMALL(A1:A30,COUNTIF(A1:A30,0)+1)

if negatives are possible:
=CHOOSE((MIN(A1:A30)>=0)+1,MIN(A1:A30),SMALL(A1:A30,COUNTIF(A1:A30,0)+1))