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?

 

[Wraithed]

Control+shift+enter, not just enter:

=MIN(IF(ISNUMBER(A1:BI1),IF(A1:BI1,A1:BI1)))

Perfect! Thanks!

 

[Aladin Akyurek]

Perfect! Thanks!

You are welcome. Thanks for providing feedback.

 

[culebrin]

Hi, I have a problem related to this thread, but I didnt know where to put it.
I have the following data:

A (DOC NUMBER) | B (DUE DATE) | C (MIN DATE)
DC0001 | 01/22/2013 | 01/22/2013
DC0002 | 08/12/2013 | 08/12/2013
DC0002 | 09/29/2013 | 08/12/2013
DC0002 | 09/06/2013 | 08/12/2013
DC0003 | 05/12/2013 | 05/12/2013
DC0003 | 08/30/2013 | 05/12/2013
DC0004 | 01/11/2013 | 01/11/2013
DC0005 | 07/21/2013 | 07/21/2013

The red column (C) is what I need to accomplish, as you can see the C column has to be calculated as the minimum date (B column) within the same doc number (A column).

Well, I have no idea how to do this (with a formula, not a macro), if you please give me some hints.

Thanks for the help!.

Omar

 

[Aladin Akyurek]

Hi, I have a problem related to this thread, but I didnt know where to put it.
I have the following data:

A (DOC NUMBER) | B (DUE DATE) | C (MIN DATE)
DC0001 | 01/22/2013 | 01/22/2013
DC0002 | 08/12/2013 | 08/12/2013
DC0002 | 09/29/2013 | 08/12/2013
DC0002 | 09/06/2013 | 08/12/2013
DC0003 | 05/12/2013 | 05/12/2013
DC0003 | 08/30/2013 | 05/12/2013
DC0004 | 01/11/2013 | 01/11/2013
DC0005 | 07/21/2013 | 07/21/2013

The red column (C) is what I need to accomplish, as you can see the C column has to be calculated as the minimum date (B column) within the same doc number (A column).

Well, I have no idea how to do this (with a formula, not a macro), if you please give me some hints.

Thanks for the help!.

Omar

C2, control+shift+enter, not just enter, and copy down:

=MIN(IF($A$2:$A$9=$A2,$B$2:$B$9))

 

[culebrin]

C2, control+shift+enter, not just enter, and copy down:

=MIN(IF($A$2:$A$9=$A2,$B$2:$B$9))

Hi!,

Thanks! It worked very well.

Is there a webpage where I can learn how this formula worked?

Thanks a lot!

 

[Aladin Akyurek]

Hi!,

Thanks! It worked very well.

You are welcome.

Is there a webpage where I can learn how this formula worked?

Thanks a lot!

You can try to find relevant information using "array formulas" as search term.

IF($A$2:$A$9=$A2,$B$2:$B$9)

yields FALSE values when a cell of A2:A9 does not equal A2, otherwise TRUE values the outer IF maps onto numbers B2:B9. The outermost MIN picks out lowest number from the latter result. Note that IF in fact creates a set of multiple values, something like:

{FALSE,FALSE,2,4,7,0.3,1,FALSE}

from which MIN would pick up: 0.3 as the end result.

 

[culebrin]

IF($A$2:$A$9=$A2,$B$2:$B$9)

yields FALSE values when a cell of A2:A9 does not equal A2, otherwise TRUE values the outer IF maps onto numbers B2:B9. The outermost MIN picks out lowest number from the latter result. Note that IF in fact creates a set of multiple values, something like:

{FALSE,FALSE,2,4,7,0.3,1,FALSE}

from which MIN would pick up: 0.3 as the end result.

Thanks! that was very very helpful,

I'm guessing, the boolean values are ignored in the formula, and the "control+shift+enter" thingy tells the formula do a "group by" for the results, well, I'll get on to that right now...

You had opened an unexplored Excel door for me, thank you very much

Omar

 

[Aladin Akyurek]

Thanks! that was very very helpful,

I'm guessing, the boolean values are ignored in the formula, and the "control+shift+enter" thingy tells the formula do a "group by" for the results, well, I'll get on to that right now...

Yes, MIN ignores non-numbers. Control+shift+enter signals Excel thet the formula in question is an array formula, i.e., one that must do array processing, creating a multiple value set.

You had opened an unexplored Excel door for me, thank you very much

Omar

The array formulas allow one to tackle indeed a different class of problems.