need opposite of MODE function in Excel

How do I find the number that occurs LEAST frequently in a range of
numbers?  This is the exact opposite of the MODE function, which finds
the most common occurrence.

For example, a column of numbers contains;

2
4
5
2
4
2

MODE will return "2" as the most commonj occurrence.  I want to know
that "5" is the least common occurence.  Obviously, my column contains
many more numbers so it's not easily to simply visualize and count
them.

Is there a way to do this in Excel?

Thanks.
0
kpalexan (1)
7/8/2003 4:08:38 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
2470 Views

Similar Articles

[PageSpeed] 0

Hi Kevin,
You can do that with the Frequency function.

      data freq bins
      2 0 1
      4 3 2
      5 0 3
      2 2 4
      4 1 5
      2 0 6


Select b2:b7, click in the formula bar and enter =FREQUENCY(A2:A7,C2:C7)
When done use <ctl><sft><enter> to create an array formula.

The resuls are 1 occures 0 times, 2 occ. 3 times, 3 occ. 0 times, 4 occ. 2
times. etc.


-- 

John

johnf202 at hotmail dot com


"Kevin" <kpalexan@hotmail.com> wrote in message
news:5a4847.0307080808.5b842678@posting.google.com...
> How do I find the number that occurs LEAST frequently in a range of
> numbers?  This is the exact opposite of the MODE function, which finds
> the most common occurrence.
>
> For example, a column of numbers contains;
>
> 2
> 4
> 5
> 2
> 4
> 2
>
> MODE will return "2" as the most commonj occurrence.  I want to know
> that "5" is the least common occurence.  Obviously, my column contains
> many more numbers so it's not easily to simply visualize and count
> them.
>
> Is there a way to do this in Excel?
>
> Thanks.


0
me5306 (285)
7/8/2003 7:04:35 PM
Reply:

Similar Artilces:

Using an excel formula in access 2000
I have a trendline formula that I need to use in my database so as to dynamically create some charts The formula is : INDEX(LINEST(y,x^{1,2}),1) Where LINEST calculates the statistics for a line using the least squares method. The Syntax for the function is Known y's, known x's, constant and stats). How can I get this function to work in access? If I need to create a function specific to access, can someone show me how? Thanks, John Petty Check this MS KB article for how to call Excel functions in Access: http://support.microsoft.com/kb/198571 John Petty wrote: >I have ...

Hexeditor needed
I have one and of course you can find many on the internet but I need one which does not try to open the file in a whole piece. I want to look into a 430 MB .iso disk image which seems to be corrupted. And reading that much data at once takes far too much time. Can anybody recommend one, before I would try them all? TIA, Hans Break the file into pieces , use emacs ( M-x hexl-mode ) C "Hans J. Ude" <hajue.ude@arcor.de> wrote in message news:h0vmtvseemtp857vaq3ee2rdm4pm6ae45f@4ax.com... > I have one and of course you can find many on the internet but I need > one which ...

How do I count several non-numerical values in Excel?
I am trying to create a formula to add the number of \ and L in a row. It seems simple but COUNT does not seem to want to do it. Help please!!! =COUNTIF(A4:F4,"\")+COUNTIF(A4:F4,"L") Change the ranges to suit your situation........... Vaya con Dios, Chuck, CABGx3 "Yuccaman" <Yuccaman@discussions.microsoft.com> wrote in message news:3D6983C4-05FC-4C3B-8C6D-46CC98D75215@microsoft.com... > I am trying to create a formula to add the number of \ and L in a row. It > seems simple but COUNT does not seem to want to do it. Help please!!! ...

Excel print out problem
I have export several files from Lotus Notes to MS XP Excel files. When the click print preview those files at Excel, the file is OK. However, if the file is more then 1 page, the 1 st row will be repeated on everypage after printing. If I delete the 1 st row, then the 2 nd row will become the 1 st row and the print out is OK. I try to clear all the format in the cell and then re-created the format at excel file but when the file print out, it will keep repeating the 1 st row again. Any help? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com...

Excel VBA #2
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C474D5.F0F10580 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Bonjour, Je croyais que chaque fois que l'on lan=E7ait une proc du type Private = Sub, toutes les variables =E9taient remises a z=E9ro. apparemment il n'en n'est rien=20 Quelqu'un peut-il me dire comment remettre =E0 z=E9ro toutes les = variables avec une instruction ou quelques lignes de code ? Merci ------=_NextPart_000_000A_01C474D5.F0F10580 Content-Type: text/html; charset="iso-...

how do I turn off requesting scan when opening Excel (Office XP)
Whenever I start Excel, it takes awhile as a message appears in the status line indicating "requesting virus scan". I have three trusted add-ins which I use which contain macros, and have reduced macro security level to "lowest", but cannot stop this action from occurring. Any suggestions would be greatly appreciated. If you're using Symantec/Norton AntiVirus, try disabling the Office Plugin that comes with it. See http://support.microsoft.com/?kbid=329820 "How to use Office programs with the Norton AntiVirus Office plug-in" for more information. ch...

Quotes add in for Excel->some options not working
Sorry for posting here but seems the most experts on this hang out here. I've been using Excel Stock Quote add in for several months in an Excel worksheet. Mostly stock quotes but 8-9 options quotes as well. Everything working fine until today when 4 of the options refuse to update at all. The corresponding stock price updates no problem as do all other quotes. But these options quotes are either wrong or not updating at all. I've made no changes. Anyone seeing anything else where that might account for this weirdness? options quotes are for AAPL, ATLS, C See ...

how can i combine 2 columns in excel into 1 without losing data?
Assuming columns A and B In C1 enter =A1 & B1 Double-click on the fill handle(black lump at bottom right corner)of C1 to increment the formula down the column as far as you have data in column B. If you would like a space between the data........ =A1 & " " & B1 Gord Dibben Excel MVP On Mon, 9 May 2005 16:26:03 -0700, sturville <sturville@discussions.microsoft.com> wrote: Not much to go on here regarding cell contents and formatting, but I'm assuming that you have text in the cells and you just want to make on long text string. (If it was numerical...

Money 2005 Activesync Help Needed
just installed 2005 Money so that it would sync with my Axim X5 running PPC 2003. I also down'd and installed the 3.8 version of Activesync, and the microsoft money for ppc download from microsoft. I can open money on both pc and ppc. In activesync options I see Money, but there is no box next to it to check. I've reset the device. Nothing changes. One thought - would it matter that I installed money on the SD card instead of the default directory? I know I had to remake a shortcut because the one it made in the programs folder on the x5 did not work. Suggestions? Yes, inst...

excel programming #2
I would like to know how to automate the following two processes with excel: 1. I would like to take data that is obtained from sql server 2000 queries results and load the detail data onto excel spreadsheets. 2. I would also like to take data that is obtained from sql server 2000 query results and load the data in pivot tables in excel spreadheet 2003. Thanks! On Sun, 17 Jun 2007, in microsoft.public.excel.charting, Wendy Elizabeth <WendyElizabeth@discussions.microsoft.com> said: >I would like to know how to automate the following two processes with excel: Have you tried the...

Excell #12
how do i creat a drop down list containing 6 items for a certain cell. The 6 items is not part of a formula. Savy, one way, use Data Validation, have a look here for details on how to do it http://www.contextures.com/xlDataVal01.html -- 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 "Savy" <Savy@discussions.microsoft.com> wrote in message news:0FDFF7E9-1303-4473-A58D-4C17625A5E56@microsoft.com... > how do i creat a...

Unable to export from Great Plains to Excel
I am currently having problems exporting a file from GP to Excel, when tryiing to export a file receiving the following error message "unexpected error: quitting" Log on to CustomerSource and look for KnowledgeBase Article 851235. I believe it describes your problem and has possible resolutions. Good Luck! Eleni "InNeedofHelp" wrote: > I am currently having problems exporting a file from GP to Excel, when > tryiing to export a file receiving the following error message "unexpected > error: quitting" ...

Excel cannot access "file name".
First dialog box show that : Excel cannot access "file name". The document may be read-only or encrypted. Then second dialog box show up : Cannot access "file name". Any idea to solve this problem? Thank you. First I would try copying the file to another directory, and changing the filename..........then see if it will open. Vaya con Dios, Chuck, CABGx3 "MG" <MG@discussions.microsoft.com> wrote in message news:00740C2C-F05D-4112-BF8F-F014CB77910D@microsoft.com... > First dialog box show that : Excel cannot access "file name". The document...

MS Access Field List Export to Excel
Probably the wrong Forum/Group but Spread the word right? Ok. Here's the deal. The wonderful creator's of MS Access have not given a way to Print or Export a "Table" or "Query" field list very easily. I have a work around that works. No Coding Involved. Step 1: Open the Database to be worked with Step 2: Click on your Table's View Step 3: Right Click on the Table to be worked with Step 4: Click "Export: Step 5: Click "Save As" Type Dropdown Step 6: Select "Text Files" Step 7: Give it a bogus name if it does not have one already. Step 8...

Excel 2007: Cannot open documents by double-clicking
Suddenly I'm getting the following error when I try to double-click on an Excel spreadsheet to open it: Windows cannot find 'xxxx'. Make sure you typed the name correctly, and then try again. "xxxx" is the full path and filename of the document I'm trying to open. Additionally, it seems that double-clicking a spreadsheet opens a second instance of Excel instead of using the currently open instance. I have no problem dragging a spreadsheet into Excel or opening a sheet from within Excel. Any help would be appreciated. Thanks, Robert Robert If the same a...

Help! Excel Formula
Below is an example of data in my excel sheet. A B C D E F Cell 1 ? cell 2 Steve 1 to 10 13/2/04 britain cell 3 Raj 11 to 17 15/2/04 india cell 4 Ricky 18 to 35 14/2/04 Spain Now if i enter a numerical value in A1 (where '?' is), i need a formula which will check the column B and produce a result as desired below Entery number = 9 then print "Steve 1 to 10 13/2/04 britain" Entery number = 12 then print "Raj 11 to 17 15/2/04 india" Ente...

Strange problem when saving excel 97 to Windows 2003 server
We just upgraded our File server from a NT 4.0 machine to a Windows 2003 Server machine (active directory + new hardware). We have several computers still running windows 98 and office 97. A couple of our windows 98+ Excel 97 users ran into a problem when saving simple (no macro) Excel documents on the server. Excel froze showing only a white screen. When checking the server folder in real time when this error occurred I saw that the document name that should have appeared in the drive was not there and instead a ever growing file named: "06D7A00" appeared. It kept growing (over 2 GB...

Analysis ToolPak - Excel 97
In Excel 97 I have attached the add-in for the Analysis ToolPak using Tools - Add-ins I then go to the Tools menu and select Data Analysis - at which point nothing happens Any ideas how to fix it so this works Thanks very much for any help Danny Which of the following are you saying? - "Data Analysis" does not appear on the Tools menu - "Data Analysis" does appear on the Tools menu, but when you select it you do not get the Data Analysis dialog listing the Analysis Tools - You do get the Data Analysis dialog, but you don't know how to use the tools on it Jerry da...

Help with find in Excel ...
Looking for a way to make the cells in column B based on the contents of cells in colum A (text string) show as below please? Column A Column B 1 1 1.1 1 1.1.1 1 1.1.1.1 1 10 10 10.1 10 10.1.1 10 10.1.1.1 10 100 100 100.1 100 100.1.1 100 100.1.1.1 100 I'd like to have a formula in each cell in column B that will return the...

Excel 2000 and VB6 controls
I was wondering if it was possible to add VB6 controls to Excel 2K. I have tried once but it tells me that the controls aren't licensed and that I can't use them. I only have three controls. Inet, Adodc, and DataGrid. Thanks, Mike ...

Excel 2000 date format cannot be set to Australian date format
-- Brian Jones Our Lady of the Sacred Heart College Bentleigh Victoria Australia Hi Brian, Check your Regional settings within Control Panel (Start > Settings > Control Panel) and make sure your location and other options are set to Australia and necessary formats. cheers, Nadia "Brian Jones" wrote: > > -- > Brian Jones > Our Lady of the Sacred Heart College > Bentleigh > Victoria > Australia ...

Need a picklist of users
Hello, I need to add a "dynamic" picklist or Lockup field (similar to Owner Field) to the opportunity form that contains all the CRM users, wich are visible for the current user. I know how to add custom fields to forms, and I know about publish with the Deployment Manager. But how can I display users within the opportunity form? Thanks, Rainer unfortunately an object is not a valid data type for schame added fields - so you can't Chris Galley Cedalion Ltd "Rainer" <rainer.schaum@bechtle.com> wrote in message news:282c01c3e1b5$a3155400$a301280a@phx.gbl.....

i need simple process if there is any
yeah u right that is a good one in Order Subform of the Northwind database but i am not that much good in Coding .. i need somthing simple like we have in Ms Excel like Vlookup function that we used or nested if function. thanks I think we missed the first part of this... On Jul 5, 12:42 pm, Naveed Pathan <NaveedPat...@discussions.microsoft.com> wrote: > yeah u right that is a good one in Order Subform of the Northwind database > but i am not that much good in Coding .. i need somthing simple like we have > in Ms Excel like Vlookup function that we used or nested if functio...

Excel sheet bar chart
Using source data from an Excel sheet I have created a bar chart on the same spreadsheet that goes across horizontally. The data is just start and finish dates of jobs. Some jobs have started and finished. Some have started and are yet to finish. Along the Value Axis along the top there are a series of datesset a week apart. I would like to pu a line or arrow along this value access that show he current date whenevr this spread sheet is opened Look here for information about adding lines to your charts: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Mic...

Excel Chart with equal length axis
Is it possible to create a chart that is square? i.e. the length of the x axis is equal to the length of the y axis and be centered in landscape? Hi, I this you are going to need VBA code to do this. This example uses the smallest length to square the chart to. '-------------------------------------- Sub Squareplot() Application.ScreenUpdating = False With ActiveChart If .PlotArea.InsideHeight > .PlotArea.InsideWidth Then Do While .PlotArea.InsideWidth < .PlotArea.InsideHeight .PlotArea.Height = .PlotArea.Height - 1 L...