# Find lowest value in range excluding 0

#### karini4

##### New Member
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?

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### IML

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP, Moderator
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

##### New Member
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

##### MrExcel MVP, Moderator
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
AB
22
34
413
59
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

##### New Member
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

##### MrExcel MVP, Moderator
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
AB
72
85
9-5
1013
Closest to 0

Replies
4
Views
73
Replies
0
Views
98
Replies
4
Views
207
Replies
8
Views
109
Replies
10
Views
256

1,181,574
Messages
5,930,696
Members
436,754
Latest member
CaptArt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back