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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Oct 10, 2002
Messages
5
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
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Nov 24, 2010
Messages
1

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
51,144
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 2, 2010
Messages
8

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
51,144
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 2, 2010
Messages
8
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
Joined
May 28, 2005
Messages
51,144
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,278
Messages
5,723,463
Members
422,498
Latest member
KAT112014

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
Top