# Find lowest value in range excluding 0

This is a discussion on Find lowest value in range excluding 0 within the Excel Questions forums, part of the Question Forums category; I have been trying to enter a formula to find the lowest value in a range, but I want to ...

1. 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?

2. 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

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

4. 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 ]

5. ## Re: Find lowest value in range excluding 0

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.

 A B C D E F 221.00 220.00 190 575 290 180

6. ## Re: Find lowest value in range excluding 0

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.

7. ## Re: Find lowest value in range excluding 0

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 | |
--------

8. ## Re: Find lowest value in range excluding 0

Originally Posted by 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 | |
--------
Welcome to the MrExcel board!

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

Closest to 0

 A B 2 2 3 4 4 1 3 5 9

 Cell Formula B2 =IF(A2=MIN(A\$2:A\$5),3,"")

Excel tables to the web >> Excel Jeanie HTML 4

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?

9. ## Re: Find lowest value in range excluding 0

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?

10. ## Re: Find lowest value in range excluding 0

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.

Closest to 0

 A B 7 2 8 5 9 -5 10 1 3

 Cell Formula B7 {=IF(A7=MIN(IF(A\$7:A\$10>=0,A\$7:A\$10)),3,"")}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

Page 1 of 6 123 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•