Find lowest value in range excluding 0

karini4

New Member
Joined
Oct 10, 2002
Messages
5
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
Thanks, this worked, but I'd like to know why in case I need to do this again! How does this exclude 0 values?
 
Upvote 0
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
 
Upvote 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.

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

Excel Workbook
AB
72 
85
9-5
1013
Closest to 0
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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?

Disable AdBlock

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