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?

 

[IML]

On 2002-10-11 10:09, karini4 wrote:
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?

array enter (that is, hit enter while control and shift are depressed)
=MIN(IF(A1:A4,A1:A4))

for a range A1:a4. If you array enter correctly the formula will look like this
{=MIN(IF(A1:A4,A1:A4))}

good luck

 

[karini4]

Thanks, this worked, but I'd like to know why in case I need to do this again! How does this exclude 0 values?

 

[IML]

Take a look at
http://www.mrexcel.com/tip011.shtml
for a good overview of array or CSE formulas.

The formula can also be written as
=MIN(IF(A1:A4<>0,A1:A4))
This basically says you want the minimum of non zeros number.
=MIN(IF(A1:A4>=0,A1:A4))
would give you the minimum of postive number (including zeros).

Back to the first formula, if you had 9,0,-12, and 7 in a range the formula would do this:
=MIN(IF({TRUE;FALSE;TRUE;TRUE},{9;0;-12;7}))
=MIN({9;FALSE;-12;7})

It will only take the minimum of the true values, which -12 in this case.

Hope that helps a little. I can tell what time it is, but don't always know how to make a watch.
This message was edited by IML on 2002-10-11 10:40

 

[vaib_sharma]

I have used the method of find lower number but its applicable for vertical ( coloum) not in horizental ( Row )

Do i need to add any prefix or sufix parameter to count the lower figure in ROW ( area

Exp.

<TABLE style="WIDTH: 282pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=372><COLGROUP><COL style="WIDTH: 47pt" span=6 width=62><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" id=td_post_111186 class=xl68 height=19 width=62>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=62>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=62>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=62>D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=62>E</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=62>F</TD></TR>

<TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" id=td_post_111186 class=xl71 height=19>221.00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl72>220.00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73>190</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73>575</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl74>290</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl75>180</TD></TR></TBODY></TABLE>

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you just need this?
=MIN(A1:F1)

If not, please try to give some more detail about what you want to achieve.

 

[lukeMV]

Very helpful board, kudos to the moderators.

My question expands upon this. i have two columns, the first column is a list of numbers (random, 0 through 42). I want the second column to find the corresponding cell that is closest to 0 (sometimes 0 does not exist) and make the value of that corresponding cell to be "3". Does that make sense?

--------
| A | B|
--------
| 2 | |
--------
| 4 | |
--------
| 0 | 3|
--------
| 9 | |
--------

 

[Peter_SSs]

Very helpful board, kudos to the moderators.

My question expands upon this. i have two columns, the first column is a list of numbers (random, 0 through 42). I want the second column to find the corresponding cell that is closest to 0 (sometimes 0 does not exist) and make the value of that corresponding cell to be "3". Does that make sense?

--------
| A | B|
--------
| 2 | |
--------
| 4 | |
--------
| 0 | 3|
--------
| 9 | |
--------

Welcome to the MrExcel board!

If negative numbers are not possible in column A then try this formula copied down.

Excel Workbook
	A	B
2	2
3	4
4	1	3
5	9
Closest to 0

If negative numbers are possible, please advise.


Also, have you considered what result you want if there is more than one number equally close to zero. For example, changing A2 in my sample above to 1 will cause both B2 and B4 to return 3. Is that what you want?

 

[lukeMV]

Actually, that works perfectly. I was thinking it had to be more complex than that.

There is an instance where I have a negative value. How would you recommend considering that? I would want "0" to be chosen, and if there is no zero to consider the next highest positive integer.

Thoughts?

 

[Peter_SSs]

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B
7	2
8	5
9	-5
10	1	3
Closest to 0