Finding the three smallest numbers

Hi NG

I can find the smallest number in a range by using SMALL, fx 
=SMALL(A1:A10;1) and so on, but how can I find the smallest value, that are 
not 0.

If I have the following numbers, 0, 1, 2, 0, 0.5, 0, 9, 2, 0, 1

I like this result =SMALL(range;1) = 0.5
Small(range;2) = 1, Small(range;3) = 1

Jan


0
Jan
5/13/2006 4:29:37 PM
excel 39879 articles. 2 followers. Follow

2 Replies
508 Views

Similar Articles

[PageSpeed] 10

Are your numbers always non-negative?

If yes:
=SMALL(A1:A10,1+COUNTIF(A1:A10,0))
=SMALL(A1:A10,2+COUNTIF(A1:A10,0))
=SMALL(A1:A10,3+COUNTIF(A1:A10,0))

If you can have negative numbers:
=SMALL(IF(A1:A10<>0,A1:A10),1)
=SMALL(IF(A1:A10<>0,A1:A10),2)
....

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

In fact, the array formula will work if the range only contains non-negative
numbers, too.

Jan Kronsell wrote:
> 
> Hi NG
> 
> I can find the smallest number in a range by using SMALL, fx
> =SMALL(A1:A10;1) and so on, but how can I find the smallest value, that are
> not 0.
> 
> If I have the following numbers, 0, 1, 2, 0, 0.5, 0, 9, 2, 0, 1
> 
> I like this result =SMALL(range;1) = 0.5
> Small(range;2) = 1, Small(range;3) = 1
> 
> Jan

-- 

Dave Peterson
0
petersod (12004)
5/13/2006 4:37:22 PM
Thank you.  The array formula did the trick.

Jan

"Dave Peterson" <petersod@verizonXSPAM.net> skrev i en meddelelse 
news:44660B42.E2799ECE@verizonXSPAM.net...
> Are your numbers always non-negative?
>
> If yes:
> =SMALL(A1:A10,1+COUNTIF(A1:A10,0))
> =SMALL(A1:A10,2+COUNTIF(A1:A10,0))
> =SMALL(A1:A10,3+COUNTIF(A1:A10,0))
>
> If you can have negative numbers:
> =SMALL(IF(A1:A10<>0,A1:A10),1)
> =SMALL(IF(A1:A10<>0,A1:A10),2)
> ...
>
> This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you 
> do it
> correctly, excel will wrap curly brackets {} around your formula.  (don't 
> type
> them yourself.)
>
> Adjust the range to match--but you can't use the whole column.
>
> In fact, the array formula will work if the range only contains 
> non-negative
> numbers, too.
>
> Jan Kronsell wrote:
>>
>> Hi NG
>>
>> I can find the smallest number in a range by using SMALL, fx
>> =SMALL(A1:A10;1) and so on, but how can I find the smallest value, that 
>> are
>> not 0.
>>
>> If I have the following numbers, 0, 1, 2, 0, 0.5, 0, 9, 2, 0, 1
>>
>> I like this result =SMALL(range;1) = 0.5
>> Small(range;2) = 1, Small(range;3) = 1
>>
>> Jan
>
> -- 
>
> Dave Peterson 


0
Jan
5/13/2006 5:29:50 PM
Reply:

Similar Artilces:

How do I make numbers become text?
I am trying to create a spreadsheet where numbers entered in one location become text in another. I tried the Help option, but I am still lost. Please help, and thank you. From earlier postings: How to Convert a Numeric Value into English Words http://support.microsoft.com/default.aspx?scid=KB;EN- US;140704& and: (courtesy of a cut and paste from a Tom Ogilvy post): If you want an addin that provides a worksheet function that does this, download Laurent Longre's free morefunc.xll addin found here: http://longre.free.fr/english/ It is downloaded in a zip file which also conta...

Excel
Is there a way to change an imported number such as: 7042626598 and display it as a phone number like: (704) 262-6598 ? Thank you, Be -- Message posted from http://www.ExcelForum.com Ben, Yes you can format the cells containing the 10 digit number to a phone number. Highlight all the required cell and on the Main menu choose Format/Cells/Number/Special/Phone Number Charlie O'Neill >-----Original Message----- >Is there a way to change an imported number such as: > >7042626598 and display it as a phone number like: > >(704) 262-6598 > >? > > >...

find data in multiple open workbooks
Im looking to find data in multiple workbooks that are all opened at once. Is there a way of doing this? ...

Item level revision numbers on POs
The Item Engineering info on inventory items in manufacturing includes revision numbers for items, however because that info is in the manufacturing dictionary we can't include through report writer on POs. I'd like to develop a routine that would copy the revision number from the item engineering table to one of the user-defined tables in inventory. Any suggestions on how to do that? Or an alternate approach to the problem? -- Jim@TurboChef Jim- This script will put the current Rev level from Item Engineering into User Category 1 on the item master. update iv00101 set usca...

can,t find my e-mail address
please help reply thank you "dc columbus robbins" <robbinsnest@localnet.com> wrote in message news:eJFE6ktBIHA.324@TK2MSFTNGP04.phx.gbl... > please help reply thank you This newsgroup is for questions and answers about Microsoft Access database software. You need to find a newsgroup for the software you are using. And, just for your information, you have to be a great deal more specific about what you are doing, and what is happening, if you expect anyone to be able to give you a helpful answer. "Can't find my e-mail address" doesn't tell ...

Conver Numbers to Log 10
How do I convert column of numbers (n=3000) to log 10 column of numbers? Assuming that your column is column A, one way: In an unused column, enter =LOG(A1) and copy down 3000 cells. Copy the column, choose column A and choose Edit/Paste Special, selecting the Values radio button. In article <0CDE6301-FC9C-488C-9D9F-3AD26B46EFCE@microsoft.com>, "Fishfan" <Fishfan@discussions.microsoft.com> wrote: > How do I convert column of numbers (n=3000) to log 10 column of numbers? ...

error numbers
Does anyone know if microsoft has a way to look up errors and their resolution by ERROR NUMBER? I have never found anything like that. Now they have something called "Events and Errors Message Center" but even that doesn't appear to let one look up errors by error number! Thanks Hi, Fredrated schrieb: > Does anyone know if microsoft has a way to look up errors and their > resolution by ERROR NUMBER? > > I have never found anything like that. Now they have something called > "Events and Errors Message Center" but even that doesn't appear to...

What is the maximum number of users that can share Excel File?
According to the Excel Help file, all users on your network, unless you restrict the number. "pfine" wrote: > ...

Filters, Can't Find Exception List In Rules
I'm trying to set up my filters for junk mail. I want junk mail with certain words in the subject sent to deleted folder. I found the filters.txt file and some of the words are already there. So apparently the filter isn't working. I tried to follow the directions on how to turn them on and they said to choose the exceptions entry in the rules wizard but I have no such choice. I know how to edit the filters.txt file but Outlook is not using it. Can anyone help? The Filters.txt file isn't actively used by Outlook -- it's really just a "readme" type file to sh...

lookup row number
Hi, can you tell me how to lookup the row number of a name in a list. EG say I have a list of names as below: Andrew Mike Steve Ian Paul I would normally just add a column with the row numbers and then lookup the row number in the second column but is it possible to do without adding the extra column by using the row formula someohow? Thanks Hi Michael Try the below.. =MATCH("Mike",A:A,0) -- Jacob (MVP - Excel) "Michael" wrote: > Hi, > can you tell me how to lookup the row number of a name in a list. > > EG say I hav...

Is it possible to find multiple data?
We have a small lotterysyndicate in work which is all logged onto an excel spreadsheet. Is it possible to search all numbers and all occurances at once, rather than individually? Ie - we have 15 lines of 6 numbers each - can i search for all 6 numbers drawn, across all 15 lines in one go? Im sure this must be possible - just dont know how. Thanks -- stewwie ------------------------------------------------------------------------ stewwie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37128 View this thread: http://www.excelforum.com/showthread.php?threadid...

Maximum number of bits
I have a requirement to find the maximum number of consecutive zeros in a row in a 32 bit binary number. For example 1111 1111 1111 1000 1111 1111 0000 1111 = 4 1111 1110 1111 1110 1111 1111 1110 1111 = 1 I am actually storing the number in a BIGINT for simplicity. I can do this in a udf with a look fairly easily but was wondering if there is something more sql that will do the job. Cheers, Michael That's an interesting question. Does it include leading zeroes? 0000 0000 0111 1000 1111 1111 0000 1111 = 9? -- Thanks Michael Coles SQL Server MVP ...

Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for records that came in between two dates. For instance, I would like to be able to pull all records input in CRM between March 1 and March 15. In 3.0 you can only query specific dates like "Last X days," "Last X Months," "On," "On or After," etc., but you cannot search the date fields between two dates. The functionality was available in 1.2 but is not available in 3.0. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the...

can one cell contain more than one independent number
A cell can contain several numbers, separated by space characters or line breaks (Alt+Enter). However, if you want to perform calculations on the numbers, it's best to keep them in separate cells. John K wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html there's also the option a using matrix formula (list of data) for example you may enter ={1;2;3} in a single cell or selection 'and validate wih CTRL+SHIFT+ENTER but the manipulation of this kinda of formula need to read more about it... ...

finding common numbers in large lists
Hello, I have two large lists of numbers (on two seperate worksheets). By large, I mean that one list is 1000+ numbers, and the second list is 3000+ numbers (each number is in its own cell). The numbers are of the form: xxxx.xxx (that is, they are precise up to three places after the decimal) 1) Is there some quick way that I can find the numbers that are common to both lists? 2) Is there a way I can find the numbers in common to both lists using only the whole number portion (diregarding what comes after the decimal of each number)? Thanks! Hi see: http://www.cpearson.com/excel/duplic...

CRM 4 Advance Finds with Windows 7
I have for some time been using CRM4 with windows 7 (RC and RTM) and have found one problem. Just wondering if anyone else has seen this and found a resolution yet. When in an advance find it will hang when selecting a pick list. For example. Open advanced find, Select Activities and use saved view [new] In the details select ActivityStatus as the Field, select equals and then click the three dots to chose the values. Select any values then click OK. At this point the page still says 'enter value' and alot of the page can not be clicked on. This will always happen on a new adv...

creating a multilevel numbering sysem
I'm trying to create a multileve numbering system ex: 100, 100.1, 100.1.1, 100.2, 200, 200.1, 2001.1, 200.2, and so on. I would like to have at least 3-4 tiers under each number that would automatically number. And you want this in Publisher? I don't think so... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "v" <v@discussions.microsoft.com> wrote in message news:C1F59342-9F2F-4E91-BE27-0BA992EB17BE@microsoft.com... > I'm trying to create a multileve numbering system > ex: 100, 100.1, 100.1.1, ...

extracting numbers from a text field and applying formula
here's my question... i have a cell that has a text field of this type: '9-19' (a-b) I want to create a formula that will subtract 19-9 (b-a) and return the result as a number// can anyone help?! is this even doable? I'd really appreciate it! PS also, i'm very fairly new at excel so a detailed response with th formula would really help! ALe ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Try: =MID(A1,FIND("-",A1)+1,99)-LEFT(A1,FIND("...

Imported Numbers not recognized as numbers
Hi there -- I've seen this problem posted a lot before, however haven't yet seen a solution posted that works for my situation. This applies to Office XP... So I copy a column of data (numbers) from a website and paste it int Excel, but the numbers are not recognized as numeric values (can't su them, etc.) I have tried all of the "easy" solutions -- changing their format t general or number, copying a 1 from another cell in a clean workboo and using the paste special/multiply (or a 0 and paste special/add function...I've tried the text to columns thing -- basicall...

One column into three
We have a table with about 40 columns and 120 Rows and we need to split each column into 3 columns only for certain rows - I'm guessing that we can't spilt a column only for certain rows as this would mess-up the entire column/row naming convention - so is there a way to automate the spliting of a column into 3 rows (i.e column A becomes A,B & C) and then merging each trio of cells (i.e. A1, B1, & C1) into one cell in the row and retaining the data that was in A1 and moving the data that was in B1 into D1 - for all 120 rows and then repeating the process for each colum...

How to find value that is bigger or smaller than 2SD?
I am new to excel, my question may be silly for you. But I really don't know how to do it and hope to get your help. I need to do statistic analysis of my data table which has lots of columns. First of all, in each column, I want to find and highlight the value that is bigger than mean+two times standard derivation, or smaller than mean-2* standard derivation. Do you have any suggestions about how to do it? Thanks alot, ming Hello, Maybe something like this ... Sub HighlightForMePlease() Dim rngLook As Range, c As Range, firstAddy As String Dim lngSTDEV1 As Double, lngSTD...

Find Related Messages in Outlook 2007
When using the "Find related messages" feature in Outlook 2003, it used to look in the Inbox, Drafts and Sent Items folder. Outlook 2007 defaults to the entire mailbox, including the Deleted Items folder. Is there a way to adjust the default settings for the "Find related messages" function? I'd like to avoid seeing messages that I have already deleted. Thanks, Mark AFAIK, no. You can sort and group by folder to make it less of a problem. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions...

Auto copy and insert a defined number of rows as defined in a cell
Hi I don't know much about VBA but could probably work something out. I need to produce carton labels from a spreadsheet using Word to mail merge and the labels need to include 1 of 10, 2 of 10 on them. The number of labels required is dependant on a number which is included for each row of data in the spreadsheet. What I am doing at the moment is copying each row and insert pasting the additional number of rows required. In the new rows I then add 1 of 10 in the first row, 2 of 10 in the second row etc. etc. This then enables me to perform the mailmerge. I have around 300...

Advanced Find and reports 02-27-06
I have a requirement to create a report (via Advanced Find) that includes the Resolution information. I have looked under Edir/Add Columns, but it is not listed. Any ideas? When you are in your advanced find, are you using Contacts in the "Look For" to start your search? Chris "AndyH" <AndyH@discussions.microsoft.com> wrote in message news:EE45046C-0695-4872-92A2-536EC1D1162D@microsoft.com... > > I have a requirement to create a report (via Advanced Find) that includes > the Resolution information. I have looked under Edir/Add Columns, but it > i...

the greater of 2 numbers
I am working on an excel spreadsheet. I am in cell C20. I want to tell IT to look at C5 and C17, determine the greater of the 2 numbers and enter the greater one in that cell(C20). Can anyone help me with an easy formula? I certainly would appreciate it. in C20 type this formula: =MAX(C5,C17) -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9848 View this thread: http://www.excelforum.com/showthread.php?threadid=512776 =MAX(C5,C17) -- HTH Bob Phillips (remov...