How do I find the minimum NONBLANK value in a sequence of cells?

How do I find the minimum NONBLANK value in a sequence of cells?
0
11/2/2005 6:31:07 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
244 Views

Similar Articles

[PageSpeed] 0

codeslinger, =MIN(A1:A10), Min will ignore blank cells if you have a number 
in one cell
-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"codeslinger" <codeslinger@discussions.microsoft.com> wrote in message 
news:69AD1DAE-BC84-47BF-8A0F-F56D2EAFD316@microsoft.com...
> How do I find the minimum NONBLANK value in a sequence of cells? 


0
11/2/2005 6:44:12 PM
=min(a1:a10)

=min() will ignore text and empty cells.

Or if you want to be extra careful:

=if(count(a1:a10)=0,"No numbers",min(a1:a10))



codeslinger wrote:
> 
> How do I find the minimum NONBLANK value in a sequence of cells?

-- 

Dave Peterson
0
petersod (12005)
11/2/2005 7:11:29 PM

"Dave Peterson" wrote:

> =min(a1:a10)
> 
> =min() will ignore text and empty cells.
> 
> Or if you want to be extra careful:
> 
> =if(count(a1:a10)=0,"No numbers",min(a1:a10))
> 
> 
> 
> codeslinger wrote:
> > 
> > How do I find the minimum NONBLANK value in a sequence of cells?
> 
> -- 
> 
> Dave Peterson
> 


Actually, I worded my question poorly. But your answers helped me to figure 
out how to do what I wanted. Thanks for the replies.
0
11/2/2005 8:12:04 PM
Reply:

Similar Artilces:

How to make a flexible cell reference?
Does anyone know how to make a reference to a cell flexible. I.e. I want a formula to get data from a cell, but want the cell to be dependant on some other value. E.g. =C"x+1" where "x+1" is a column number dependant on the value of x. I sure hope anyone can help me with this! Mark =INDIRECT("C"&x+1) -- HTH Bob Phillips "Mar Vernooy" <Mar Vernooy@discussions.microsoft.com> wrote in message news:40D873BD-1E6A-4412-BFCD-6A8B57B73E6F@microsoft.com... > Does anyone know how to make a reference to a cell flexible. I.e. I want a >...

Interpolating value
When a chart generated is it possible in exel to return a value from the chart or chart data by reading chart or interpollating value from knowns. (parabolic/logaritmic charts etc) W The chart can't, but the data sheet (from which the chart was generated) can. Try the FORECAST function. -- David Biddulph "willemeulen" <willemeulen@discussions.microsoft.com> wrote in message news:B885B130-15A9-43C6-B898-E6C157981790@microsoft.com... > When a chart generated is it possible in exel to return a value from the > chart or chart data by reading chart or ...

I lost a paragraph. How can I find it?
I was typing a speech I am to give in two weeks and it was single spaced. I decided for editing purposes that I wanted it double spaced. I went to the help section in my Word 2003 edition and it told me to select one paragraph at a time, which I did. I then fumbled around with the instructions but finally came across a box and an icon that had the double space I was looking for. I selected the icon and when I returned to the document, the paragraph I selected had disappeared, but the rest of my speech was, in fact, double spaced. How do I retrieve/find my selected original p...

How do you write 2 lines of text in 1 cell rather than use 2 cells
I am using Excel 98 and want to know how to get 2 lines of text in 1 cell rather than space it out over 2 cells. See below for example if you dont get it..... This is what (cell no.1) I mean. (cell no. 2) I want to be (cell no.1) able to do this. (cell no.1) Please help.....thanks Type part, then Alt-Enter, then the rest. -- HTH Bob Phillips "!!!help!!!" <!!!help!!!@discussions.microsoft.com> wrote in message news:33CF64B2-E51F-4E8F-9680-1B495146C134@microsoft.com... > I am using Excel 98 and want to know how to get 2 ...

how do you add a new number to each cell in a column
for instance in column c from row 5 to row 455 I want to add .27 to every number that is already in each cell example c5 has 9.09 already in it, I want to add .27 for a new total of 9.36 I then want to copy that formula so that it does the same thing all the way to c455 Hi JCE, Select a Cell and Type =C5+0.27 where C stands for column and 5 stands for row drag the active cell downward to see the next row result then you may copy the formula to other worksheets or cells buy using copy and specialpaste. Hope this helps. "JCE" wrote: > for instance in column c from row 5 ...

How to get Cell contents in scrollbar mouseover, not cell#
Hello all - I have a large Xcel 2000 file with last names in column 'A'. I'd like to use the scroll bar and see the actual cell value rather than the cell# in the mouseover, as the cell# is not terrifically valuable information. Any hints or insights would help. Thanks and regards, Bob ...

Copy from merge cell
When I copy (Cell B2) from a merge cell for example A1 to S1 the result is ######### When I delete the range and copy from A1 it work, but when merge cell (A1 to S1) is blank "0" appear is B2. How to get rid of the "O" Try formatting that receiving cell (B2?) as General. Cells formatted as Text have problems when the length of the string is between 255 and 1024. MK wrote: > > When I copy (Cell B2) from a merge cell for example A1 to S1 the result is > ######### > When I delete the range and copy from A1 it work, but when merge cell (A1 to > S1) is blan...

how do I assign a cell equal to another cell
Hai , I facing a problem in Office 2003 , when I pres = then go to the cell that I want , it will come out a formula , anyway to let it come out as cell name (ie =D5 at D20 cell) Thanks Hi try tools / options / view and untick formulas the other option is that your cells have been formatted as text select a blank cell in a new worksheet / workbook and copy it select the misbehaving cells and choose edit / paste special - add cheres JulieD "yl" <yl@discussions.microsoft.com> wrote in message news:CDE3D991-0530-4B36-9BEF-DA1EF8AF8D11@microsoft.com... > Hai , I facing...

Count cells with data
I receive large databases each month. I have been able to format the data using PROPER. Though when I follow the instuctions from "3 formatting shortcuts" it calls for dragging the fill handle to the end of the new column to show all the converted cells. My databases are hundreds of names long! Is there a way to count the cells with text in them in advance - without scrolling down to the bottom - and then put the range into the =PROPER (range of cells with data) command? -- Thank you kindly You are using a second column to convert to Proper? You can Double-Click the Fill...

store some of the information from one cell into another
I would like to store some of the information from one cell into another. For instance, information in one cell has StampPDF V 2.3. I would like to cut 2.3 into a different cell. I was wondering if anyone new of the quickest way to do this. I have about 2000+ cells to work with. If the information you want from the cell is always at the end, and is always the last 3 characters you could use =RIGHT(A1,3) assuming that the original information is in cell reference A1 If you actually want to remove the 2.3 from the original cell then "bsantona" wrote: > I would like to st...

Copy/Cut/Paste of Merged Cells in Shared Protected Workbooks
Hi I have a shared protected workbook into which comments and data are input on a daily basis to merged cells. In some cases the same information just has to be copied and pasted into a new position in the same worksheet. When the cell is copied and pasted the cell de-merges in the new position. Is there anyway of preventing this? The only alternative I can currently see is to copy or cut the text in a cell from the formula bar and then paste this into the new cell. Is this my only option? Any advice much appreciated Dave ...

how can I get a cell to change color by date to indicate expiery t
I'm trying to get the cell color to change from green to yellow to red as a date entered in the cell gets closer to that date plus 12 months or 36 months. ie some task was completed on 1 Jan 08 and will last for 24 months so I want the cell to read green if the date is still good turn yellow a month or 2 before it expires and turn red and clear the date if expired. So if the cell is empty it should show red. But I want to be able to update the date the task was completed ie it was completed again on the 15 Dec 10 before it expired so I want the countdown to expiration to restart f...

Can we use the datediff function and put the answer in a cell?
what we want to do is return the difference of this function into a cell in a table, can we do it? On Sat, 12 Dec 2009 11:34:01 -0800, timmone <timmone@discussions.microsoft.com> wrote: Yes, but you shouldn't. Because that would violate an important relational database design principle that says "no calculated fields in the database". Rather you would calculate the value on the fly in a query: select DateDiff("d", myStartDate, myEndDate) as DaysBetween from myTable -Tom. Microsoft Access MVP >what we want to do is return the difference o...

how can i make paragraphs in side one cell in Excell?
Hi press ALT & ENTER when you want a new line Cheers JulieD "ashraf" <ashraf@discussions.microsoft.com> wrote in message news:E508A2C8-CC79-4107-93EE-5D607A53E8BA@microsoft.com... > or, format cell / alignment / wrap text ...

Cell Formatting #12
Entering a bank sort code into a cell i.e 16-00-04, however it keeps changing to a date range. I have tried converting the field to text and then change the format, doesn't work. I have also tried copying the format from the cell above and then overtyping it, this doesn't work. The only way I could get round it was to start the string with a '. Thanks for the help. Hi try formating the cell PRIOR to entering data in it as 'Text' >-----Original Message----- >Entering a bank sort code into a cell i.e 16-00-04, >however it keeps changing to a date range....

How to add a space after each text value in all the cells in a row
I want to add a space (or any character(s)) after each text value in all the cells in a row or in a column For example, cell A2 contains "ABC", cell B2 contains "DEF", and so on. I want to make all the cells in row 2 to have a space so cell A2 contains "ABC ", cell B2 contains "DEF ",... try going to an empty part of the sheet (say row a4) & type =a2&" " or "[any character]" then copy that across the row for your range. after calc you can copy the range & paste special (values) back into range a2 ...X2 &am...

when does money 'find out ' about new funds
I'm looking at a new fund "Fidelity 130/30 Large Cap Fund" (symbol =FOTTX) and although I can get quotes/info via Google/Yahoo/About / etc , Money can't find it. So if I were to buy it I would have to manually update it .... for how long ? anyone know ? In microsoft.public.money, - Bobb - wrote: >I'm looking at a new fund >"Fidelity 130/30 Large Cap Fund" >(symbol =FOTTX) >and although I can get quotes/info via Google/Yahoo/About / etc , Money >can't find it. So if I were to buy it I would have to manually update it >... for how lon...

Graphing With Large Variation In Values
I'm trying to make a simple bar graph to display the results of some data that has 10 data sets. However, there is one data set that has a much higher value that the other nine. The result is that the data sets with lower values all appear to be the same value and her hardly distinguishable because the scale is set by the highest data set. Is there a way to "cut out" the middle section of a bar graph that will show the smaller data sets with better resolution? Thanks in advance Hi see: http://www.tushar-mehta.com/excel/newsgroups/broken_y_axis/index.html http://peltiert...

How to get the value of the prompt environment variable
I need a way to get the value of the %prompt% environment variable. I would prefer it actually if I could get the value from a more reliable source as this is a console app I'm writing. -- ClassicVB Users Regroup! comp.lang.basic.visual.misc Free usenet access at http://www.eternal-september.org Leo formulated on Wednesday : > I need a way to get the value of the %prompt% environment variable. I would > prefer it actually if I could get the value from a more reliable source as > this is a console app I'm writing. Scrap the more reliable way section as i...

Calculation query, how to avoid extreme values?
Good morning, I have 1 table with a few fields (A, B, C) that I'm using for various calculations. For various reasons are some of the input "weird" and I want to leave that out of the calculation (average). For example, Field A requires > 0 AND < 100, B < 400 Now, if one record doesn't fullfil A requirement I don't want to use it for A's average calculation BUT I want to use it for B's average calculation (if it satisfy B's requirement). This means that if I use the query criteria A: >0 AND <100 it will filter out all other records an...

Too Many Headings, Values for Chart Feature To Handle???
I'm having problems setting up a chart with the following headings (see below). The Chart Wizard will only let me select so many headings (& values). If I CTRL - click one heading too many, the list field window clears and shows the last cell selected. Any ideas what's happening here? Column Headings: Aerosol, Art, Batch, Bulk, Dykem, Mark-Tex ,Outsource, Quality, R&D, Receiving, Scrubs, Shipping, WHC Colimn Subheadings: DEV, NCP Row Headings: BATCHING CODING FILLING HANDLING HAZCOM IMPROVEMENT INVENTORY LABEL DEFECT PACKAGE DEFECT RAW MATERIAL RECEIVING ...

Help with Audit, Can't assign value
I have a form which is used to input customer data and has a control which is set to record the current user using the CurrentUser() function tied to the Before Insert event of the form. The same form contain a lookup button with which I can close the form and reopen it with an openargs that causes the form to us a dataset tied to a query which requests the customer last name and returns matching records. The problem is that when I click the button to look up customers I get an error stating that "You can't assign a value to this object". In the VBE the line that I used to assig...

I would like to extend hyperlinks across cells
I want to extend the hyperlinks across all cells in one worksheet..... like cell A1 in sheet 1 should point to cell A1 in sheet2 and so on..... i have 500 cells in both sheets.... Select Cell>right click>hyperlink>in this file>sheet>cell ref. Hope this is what you mean. Pat F "venkat" wrote: > I want to extend the hyperlinks across all cells in one worksheet..... like > cell A1 in sheet 1 should point to cell A1 in sheet2 and so on..... i have > 500 cells in both sheets.... Ya this will enable hyperlink for one cell... i would like to drag this acro...

Comparing / Finding text in Lists
Hi If I have three main columns of text values (i) Company ID (ii) Well ID and (iii) plate ID and say I want to select 153 out 332 wells using another (smaller) list how would I do this in Excel? Many thanks Yours Andrew J Scotland Without seeing your data I guess one way would be to use another colum in your main list with =VLOOKUP() formula into your small list and the filter or sort on this column. A formula like :- =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$D$38,2,FALSE)),0,1) will put a 1 if found or 0 if not -- Message posted from http://www.ExcelForum.com Hi Have a look at Chip'...

Hiding a total until all values are filled
I want to forcast certain dates based on a persons age. For example, my daughter was born on 11-5-96. I want to forcast her 8th, 12th, and 18th birthday. I am wanting to do this for a hundred or so youth at our church. So, I put in the following =DATE(YEAR(B1)+8,MONTH(B1),DAY(B1)) in A1. The problem I have is I have an incorrect date in A1 until a date is entered. Excel wants to put in 31-Dec-07 if B1 is blank. How can I keep A1 blank until I have entered B1 without entering it all in one at a time, the whole of column A will have the same equation with regards to its row. I am just new...