How do I find the minimum value in a range while ignoring zeros?

-- How do I find the minimum value in a range while ignoring any zeros in 
that range using Excel 2007?

4/6/2010 2:44:01 AM
excel.worksheet.functions 4936 articles. 0 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 7

If the numbers are *always* positive..

Array entered**:


Or, normally entered:


If there might be negative numbers...

Array entered**:


** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

Microsoft Excel MVP

"Ted B." <> wrote in message
> -- How do I find the minimum value in a range while ignoring any zeros in
> that range using Excel 2007?

4/6/2010 2:57:08 AM
try this.....


"Ted B." wrote:

> -- How do I find the minimum value in a range while ignoring any zeros in 
> that range using Excel 2007?
4/6/2010 3:21:01 AM
You could use a conditional MIN, something like this in say B2, array-entered 
ie press CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing 
Success? hit the YES below
"Ted B." wrote:
> -- How do I find the minimum value in a range while ignoring any zeros in 
> that range using Excel 2007?
4/6/2010 3:22:01 AM
forgot to mention.
adjust ranges to suit your data.


"Ted B." wrote:

> -- How do I find the minimum value in a range while ignoring any zeros in 
> that range using Excel 2007?
4/6/2010 3:22:03 AM

Similar Artilces:

Trapping a NO FIND after a find
I use the code below to store a row number to a variable after a find. I would like to trap a NO FIND if the find is unsuccessfull Any ideas. FSt1 provided the code below Sub macfindrow() dim rn as string dim rng as range dim therow as long rn = inputbox("enter something to find") if rn <> "" then Set rng = nothing Set rng = range("A1:IV65536").Find(what:=rn, _ After:=Range("A1"), _ Lookin:=xlformulas, _ Lookat:=xlpart, _ ...

I can't find my hidden emails
How do I retrieve my hidden email folder. I have windows mail using vista version. To see your storage location, Open Windows Mail, go to Tools - Options - Advanced - Maintenance - Store Folder It's normally C:\Users\<yourlogin>\AppData\Local\Microsoft\Windows Mail \Appdata is normally hidden Email messages are stored as individual .eml files. -- Dave N. MS-MVP (Mail) Windows 7 Ultimate "Hatsoff" <> wrote in message

cannot find database
I have an excel spreadsheet that is supposed to update a access db. Whenever I try to save the .xls I get an error stating cannot find db. Even when I open the db with access, I get the error and the db opens anyway?????? This only happens on 2 out of 20 pc's and I cannot figure out why???????? Thanks ...

Insert duplicate rows based on numeric value in column
Is there a way to insert new duplicate rows in an excel worksheet based on a value in a column? For example I have 2 columns as follows Place Number London 3 Paris 5 Lisbon 2 France 2 I want to achieve the following Place Number London 3 London 3 London 3 Paris 5 Paris 5 Paris 5 Paris 5 Paris 5 Lisbon 2 Lisbon 2...and so on So I basically want the first column and its value repeated the number of times specified by the associated value in the Number column. I hope that makes sense. I hope someone can help as this is driving me nuts and I a...

MAX Value and OFFSET?
Hi Aladin, Picked up correction. Thanks Tin´┐Ż Aladin Akyurek Wrote: > Aladin Akyurek <> wrote i > message news:<>... > > =INDEX(Test_Score,MATCH(MAX(INDEX(Test_Score,0,2)),INDEX(Test_Score,0,2),0),1) > > > > However, this is not good enough when there 2 or more instances o > the > > max value. Consider: > > > > {"X","Y";"jon",40;"damon",35;"olga",28;"sandra",40} > > > > in A3:B7...

Find (but not find)
My program takes a name from sheet3 goes to sheet1 to Find the name. If it cannot find name, how do you do an If/End to Exit Do while or find out if name has been founf? I have "On Error Resume Next" in program. Thanks again for all your help Gordon As ALWAYS, post your code for comments & suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software "Gordon" <> wrote in message > My program takes a name from sheet3 goes to sheet1 to Find the na...

Reading File and finding information
Hi, Basically i wish to do a search and find function in a file and use th information to place data in a secondary file with a matching value. I have 2 excel files that i want to extract some information from. I have an excel file which contains lots of different values, fro values 10001 -40001. In the other excel (old csv) files i have th same numbers with associated text with it. I want to have the fil containing the data with text attached searched through and the th matching data copied to the main excel sheet. The sheet containing the values and associated text may contai additiona...

Advanced Find doesn't work
After having updated my Office 2000 version with service release 1a and service pack 3 the Outlook 2000 advanced find function no longer works. It doesn't deliver any results and pretends to continue searching forever. Anyone any ideas about this problem? Thanks, Steve Same problem here....except it doesnt seem to keep searching. It will only find something within the last day or so. "Steve Kraft" <> wrote in message news:00a501c3c472$93d71c90$a601280a@phx.gbl... > After having updated my Office 2000 version with service > rele...

Combo box not showing correct value when changing records on a form
I have a form frmBids with two combo boxes showing a customer (cboCustomer) and contacts for each customer (cboContacts). I got the cboContacts to only show the contacts unique to the customer by using a requery code on the AfterUpdate of cboCustomer. That works fine. However, when I move between records only the contact shown on the first record displays, it does not update on subsequent records. If I go back and select the customer from cboCustomer it updates and displays the correct contact, but then the next record just shows the one I just selected. I verified that when I switch betwe...

Is a value a member of an array?
I need to test if a value is a member of an array. For example A1:A300 is an array containing data in no particular order. B1 is a value that may or may not be one of the entries in column A. In C1, I would like TRUE if B1 is a member of A1:300 and false if B1 is not. I can't find an easy way to do this. Any help is appreciated. Thanks, Try this in C1: =COUNTIF(A1:A300,B1)>0 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------...

how to change the pivot chart automaticaly as values in the worksheet change
Hi, I would like to know how I can make the pivot chart change automaticaly as I change the values in the work sheet? I also would like to know how can i merge file from such pivot chart/s? Jeevan ...

find efficeincy #2
Hi i need to find the efficeiny of a project i have 60 minutes availalble i have a target of 78 if i have no stoppages and produce 78 i am 100% if i have 30 mins stoppages and produce 39 i am 100% if i have no stoppages and produce 39 i am 50% can anyone tell me an easy wy of calculating this thanks kevin ...

Define A Range Named Percent
I am working on an Excel practice text and I have been asked to "Define a range named percent to all the percent values in column E". Column E looks like the following: SAT Average 0.840935673 0.714285714 0.914634146 0.930232558 What do I do? Thanks, CJ Select the range E2:E4 In the name box (to the far left of the formula bar) you'll see where it says: E2. Click in that box and type: Percent Hit ENTER Now, check out the named range you just created. Select any cell Click the drop arrow in the name box Click on Percent and see what happen...

Finding no of elements in the pointers
Hi Friends, I have one problem... My problem is how to determine the size of the pointer. (No of elements) For eg. i have the string pointer... after some process the pointer contains some no of string elements. so now i want to know how many strings reside on the pointer... string* pstrArray; .... .... .... // now pstrArray contains some no of string elements. -> now how to know the no of elements in the pointer? for normal array we can find this by using sizeof(array variable)/sizeof(type of array) but for this pointer i don't know how to find.... thanks in advance, sarang. ...

Enter Parameter Value Error
The following code opens a popup "Frm_LUComplianceHistory" form from a Subform "Frm_LOCAct". stLinkCriteria1 matches account number fields in both of these forms This works fine. Then stLinkCriteria2 is supposed to match product category fields in the main form (LOC_Maintenance.txt_GrpProdCat) and the popup form (Frm_LUComplianceHistory.Product). However, when I doubleclick on cbo_LOCAct_AccountNo, I get an "Enter Parameter Value" dialog box with the product category that it should be filtering on above the text box. If I type in the product category ...

Averaging every 5th cell while omitting zeros
Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. Erica, I don't li...

Finding data in grouped sheets
When using the 'find' option whilst workbook pages are grouped together I am getting data from the last page first, then the last but one etc. Can anyone tell me whether it is possible to change this so that it finds the data in the first possible page, ie it looks at page 3 before page 4 etc? What I am trying to do is set up a spreadsheet which finds the first 'vacancy' in a childrens nursery. As I have set it up at the moment it is finding the last vacancy first! All help greatly received! Thanks in advance. Susie Vaughan This may not quite fit, but you may find it very us...

Query SQL server db with cell value parameter
I want to supply criteria value using a cell on the spreadsheet to contain a date. Can't seem to figure out how to do this. On the Connections - Properties - Definition dialog, "Parameters" is grayed out. ...

Minimum Rights to use Batch Recovery
What are the Minimum Rights required to use Batch Recovery ? Security rights to Batch Recovery must be set for the user or class. That's all. Frank Hamelly MCP-GP, MCT East Coast Dynamics Thanks, I went into the AP security role the individual was assigned and added/Checked the Batch Recovery and Master Post right. "Frank Hamelly, MCP-GP" wrote: > Security rights to Batch Recovery must be set for the user or class. > That's all. > > Frank Hamelly > MCP-GP, MCT > East Coast Dynamics > > ...

Procedure to remove all named ranges
I have a workbook which the last step is to email a single sheet to a user, which works fine. I was also able to delete all vba out of the sheet using a procedure. The one thing I cannot get to work is to delete all of the named ranges out of the sheet because when you go to Insert name define they all show up. How can I programmatically remove all named ranges. Thanks -- DoctorV ------------------------------------------------------------------------ DoctorV's Profile: View this thread:

Find old categories
I merged some categories and when I tried to undo the merge - it removed all the categories and put everything under Categories: (none) How can I find out what categories I had these under before??? ...

How do I find cells that being with a blank?
::Hi, I have a spreadsheet with circa 11,000 rows, approx. 6 columns. Some of the data MAY contain values that begin with a blank space, e.g. the cell may look like (ignore quotes) \" Fred\" when it should be \"Fred\" Is there an easy way to change the cell colour to Red for all cells in a spreadsheet where the first character is blank ? Thanks in advance for your kind help. Matt:: -- bvinternet ------------------------------------------------------------------------ bvinternet's Profile:

2 y-axis want to use a macro to tighten the range on both
is there a way i can tighten the range using a macro for two y-axis in the same chart. Please explain more. Do you want a secondary axis? Do you want to change the scale on the axis/ -- Bernard V Liengme remove caps from email "HBS" <> wrote in message > is there a way i can tighten the range using a macro for two y-axis in > the same chart. > ...

find, copy or there about
Hi All I am looking for a way to pick certain records out of an excel database. The string I am looking for can be imbeded in a feild either at the begining end or middle and neither the string nor the feild are predefined. After I have found the records that match the crieteria I need to perform certain actions upon them. An addin would be nice that copies according to crieteria. I vaugly remember an Excel function that does something like that but cann't remember exactly which one. I thank you all Erle It sounds like data|Filter (either Autofilter or Advanced filter) might work. Or ...

Advanced Find not finding all
"Advanced Find" in Outlook 2007 is not finding all email messages which match the criteria. It finds between 30 and 200 and then stops. I then have to work with those (e.g., delete them) and then click "Find Now" again to find another batch, dozens of times! What is wrong? How do I fix this and find all messages that match the criteria? My typical application is to split the entire contents of my .pst files by date. Do do this I make a copy of the file. In one I delete all messages older than the cutoff date (e.g., 1/1/2007), and in the other I delete all messages yo...