Trying to find out which numbers occure most often

I�m giving exams to students and want to see if the students are missin
the same questions in every class.  I have a simple spreadsheet tha
has me enter the question number into the column each time a questio
is missed.  The MODE function can tell me which number is occurrin
most often.  However, I need to see if there is any other numbers tha
are occurring often as well.

Is there a command that will allow me to look at the second mos
entered number?  For example, if I have 5,3,3,2,3,4,3,4,1,3,4, my mod
would be 3 as that�s the number that repeats the most.  However, 
happens 3 times and that is a number I need to be concerned with a
well.

Hopefully someone has a suggestion for this.  Thanks to all of you fo
reviewing my question and providing feedback.

Joh

--
Message posted from http://www.ExcelForum.com

0
12/15/2003 1:03:11 PM
excel 39879 articles. 2 followers. Follow

3 Replies
436 Views

Similar Articles

[PageSpeed] 40

One way you could do this is with a result table for each question
using the countif function.

i.e.
A          B         C
1       Q No    Count   Rank
2        1
3        2
4        3
5        4
6        5

In B2 type the formula =countif($A10:$A$20,A2) where A10:A20 is you
input data. Copy this to B3, B4 etc

In C2 type the formula =RANK(A2,$B$2:$B$6) this will give you th
position of each question in the count.

Hope this helps

Mik

--
Message posted from http://www.ExcelForum.com

0
12/15/2003 1:34:27 PM
On Mon, 15 Dec 2003 07:03:11 -0600, yitbos <yitbos.yhc2a@excelforum-nospam.com>
wrote:

>I�m giving exams to students and want to see if the students are missing
>the same questions in every class.  I have a simple spreadsheet that
>has me enter the question number into the column each time a question
>is missed.  The MODE function can tell me which number is occurring
>most often.  However, I need to see if there is any other numbers that
>are occurring often as well.
>
>Is there a command that will allow me to look at the second most
>entered number?  For example, if I have 5,3,3,2,3,4,3,4,1,3,4, my mode
>would be 3 as that�s the number that repeats the most.  However, 4
>happens 3 times and that is a number I need to be concerned with as
>well.
>
>Hopefully someone has a suggestion for this.  Thanks to all of you for
>reviewing my question and providing feedback.
>
>John
>
>

Well, you could get the second most entered number with the *array-entered*
formula:

  =MODE(IF(rng<>MODE(rng),rng,""))

To *array-enter*, hit <enter> while holding down <ctrl><shift>.  XL will place
braces {...} around the formula.


--ron
0
ronrosenfeld (3122)
12/15/2003 3:06:29 PM
John,

A pivot table, using the question number column both in the row (or column)
section (which means group by that column) of the pivot table, and also in
the data area, set to COUNT, will give you counts of all the questions
missed.  Then sort them descending.  Perfect for this application.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"yitbos" <yitbos.yhc2a@excelforum-nospam.com> wrote in message
news:yitbos.yhc2a@excelforum-nospam.com...
> I�m giving exams to students and want to see if the students are missing
> the same questions in every class.  I have a simple spreadsheet that
> has me enter the question number into the column each time a question
> is missed.  The MODE function can tell me which number is occurring
> most often.  However, I need to see if there is any other numbers that
> are occurring often as well.
>
> Is there a command that will allow me to look at the second most
> entered number?  For example, if I have 5,3,3,2,3,4,3,4,1,3,4, my mode
> would be 3 as that�s the number that repeats the most.  However, 4
> happens 3 times and that is a number I need to be concerned with as
> well.
>
> Hopefully someone has a suggestion for this.  Thanks to all of you for
> reviewing my question and providing feedback.
>
> John
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere8060 (363)
12/15/2003 8:40:08 PM
Reply:

Similar Artilces:

Error when I try to delete messages
This is a multi-part message in MIME format. ------=_NextPart_000_0015_01C549A3.5919FF20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable When I delete any message from any folder or pst, I get the error: "This item could not be deleted. It was either moved or already deleted, = or=20 access was denied." The message gets deleted but the error pops up every time. Anybody know how to fix this? I'm running Outlook 2003, SP1, no Exchange = Server. Thanks ------=_NextPart_000_0015_01C549A3.5919FF20 Content-Type: text/html; ...

Sequential Number Generator, Saving, and Linking
Hello, not totally familiar with Excel and trying to accomplish th following within our sales department: 1. We have two basic Excel files that we have to access on a share drive every time we issue a quotation: FILE A: This is an Excel quotation form already built. FILE B: This lists out the existing quotations that have been issued listed by reference number. It includes basic fields like 'Sale Person', 'Product' and 'Date', etc. Every time we issues a quote we fill out the nessary information i FILE A, reference FILE B to determine the next sequential number. W s...

Formatting cell for phone numbers
I am trying to format a cell with phone numbers so that it will look like the following: (937) 123-3456 (937) 129-9876 fax or (937) 123-3456 The word 'fax' is more specifically a label, not necessarily that word. I would like to have carriage returns between the numbers with the possibility of several numbers. This may be confusing and/or too complicated, but I'd like to give it a try. If anyone as suggestions or possible solutions, I would appreciate hearing them. Thanks, Craig >-----Original Message----- >I am trying to format a cell with phone numbers so that ...

numbering #2
Is it possible to apply numbering (invoice or booking numbers) to sheets inside of a book. Four people using the same books and we want to put number on the sheets without becoming a number management nightmare Like adding a series of numbers like invoice number Each sheet having it's own number in the series but not repeting on different sheets. Basically generate an invoice number on the sheet when copied from a templete or at print time ...

Display page number in a cell
Is it possible to display the page numbering in a cell instead of header/footer? This would require some codding to find the pagebreaks and enter a sequential number in a cell above each page break. Have a look at this thread. http://snipurl.com/9ffk Note David MCRitchie's links to Myrna Larson code. Gord Dibben Excel MVP On Wed, 29 Sep 2004 07:13:04 -0700, sbuddy <sbuddy@discussions.microsoft.com> wrote: >Is it possible to display the page numbering in a cell instead of >header/footer? ...

Adding up numbers from columns.
How can I sum up multiple columns while not letting each cell "associated values" sum to a number greater than one specified. For example, if I have sets of colums: 14 3 12 2 11 2 11 1 10 1 and 20 5 19 4 18 3 17 2 and 25 6 22 4 20 3 18 2 I want to know what 3 combination of numbers in the left 3 colums giv me the highest value...WITHOUT letting the correspoding numbers in th right hand column be greater than, say 10. Thanks, Larr -- Message posted from http://www.ExcelForum.com ...

Page numbering worksheets
Is it possible to number the pages in a workbook if l have multiple worksheets? I would like to number each worksheet within the workbook but l need the numbers to flow on from the previous sheets so l don't have to manually change the number on each worksheet. thankyou "H" <Hayley.Slater@emap.com> wrote in message news:06f501c3505e$df329d50$a001280a@phx.gbl... > Is it possible to number the pages in a workbook if l have > multiple worksheets? I would like to number each > worksheet within the workbook but l need the numbers to > flow on from the previous...

NewBie: trying to get a Username from a console App
I am trying to get username on a console app with MFC support . Here is the code #include "stdafx.h" #include "spy.h" #ifdef _DEBUG #define new DEBUG_NEW #undef THIS_FILE static char THIS_FILE[] = __FILE__; #endif ///////////////////////////////////////////////////////////////////////////// // The one and only application object CWinApp theApp; using namespace std; BOOL GetUserName( LPTSTR lpBuffer, // address of name buffer LPDWORD nSize // address of size of name buffer ); int _tmain(int argc, TCHAR* argv[], TCHAR* envp[]) { int nRetCode ...

Where to find documentation on IDispatch errors?
Hello again, I am trying to figure out how to look up IDispatch errors. I couldn't find the documentation that explains IDispatch errors by their error ID (ex: #32). Can anyone point me to documentation where I can find a list of IDispatch errors and their descriptions? The error I'm trying to figure out the meaning of is IDispatch error #32. Thank you, Mete You did better post this to a programmer's group... IDispatch is a COM interface. It is documented in MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/automat/htm/chap5_3alw.asp. Typically IDispatch ...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

0 number
Hi, social security number has 7 numbers. However, when I type 0454636, 0 will be gone. Ex: Type 0454636. It will become 454636. I have almost 8 columns stored social security numbers. Please show me how to fix the problem. Thank you Loi Try format cells -- custom and type 0000000 in the box. >-----Original Message----- >Hi, > >social security number has 7 numbers. However, when I type >0454636, 0 will be gone. > >Ex: Type 0454636. It will become 454636. > > >I have almost 8 columns stored social security numbers. >Please show me how to fix the prob...

Try that critical pack from Microsoft Corporation
--zyqwganxt Content-Type: multipart/related; boundary="ozwztwhan"; type="multipart/alternative" --ozwztwhan Content-Type: multipart/alternative; boundary="gmqsphxspxalotm" --gmqsphxspxalotm Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS User this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protect your computer. Thi...

The number zero
I have part numbers that begin with zero. How do I keep the zero present in the cell? Judy, Start your entry with a single quote mark, or format the cell for Text prior to entering your data. HTH, Bernie MS Excel MVP "Judy" <anonymous@discussions.microsoft.com> wrote in message news:44B9D6D2-B169-4ABE-BBFF-2C615585DB1A@microsoft.com... > I have part numbers that begin with zero. How do I keep the zero present in the cell? ...

import- number- sort?
I imported this data into a spread sheet- I tried to format it to number, but when I sort it- I get : 37.08 4.33 4.79 4.8 42.56 5.94 Whats up with this? Thanks Brent Hi Brent, Install the TrimAll macro, select the column and invoke the macro from Alt+F8 http://www.mvps.org/dmcritchie/excel/join.htm#trimall Directions to install and use a macro at http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro I expect that you have spaces or CHAR(128) "Required Blank" characters in you data you can check is =LEN(A1) --- HTH, David...

Random Numbers Generation
How can I get random generation of numbers between 1-52 to allocate into 4 groups of 13, without any duplication of any number?? Can this be achieved by a formula?? Thanks, Bob. ...

High number of page faults per second, but has enough memory?
Hi, I'm having a problem with an application, it easily has enough physical RAM but it's generating a very large number of page faults (2000 per second). Does anyone know why this might be? ...

numbers formating problem
I type 10 I got 1 I type 11 I got 1.1 Where from this problem cames? This is not a formatting problem, the value is what you see; 1, not 10. Tools > Options menu, "fixed decimal" is selected. HTH. Best wishes Harald "barsha" <barsha@discussions.microsoft.com> skrev i melding news:A62D8395-FD84-40F3-A867-B391D5956A57@microsoft.com... > I type 10 > I got 1 > I type 11 > I got 1.1 > Where from this problem cames? > Hi Barsha, Try: Tools | Options | Edit and uncheck the fixed decimal option. --- Regards, Norman "barsha" <...

CRM 3.0
I would like to move the ordernumber middle part to a larger number to match the next available number in the existing paper order system. Where do I find the row/column in the database to change the seed or next available number? ...

Unable to find CRM Instance
It appears as if other people have experienced the "Can't Find CRM Server" installation error before; however, I don't see any posts on how to fix it. Please help! I've been using the CRM Server for some time now with no problems. Every time I try to install SFO, I get the following error: Setup was unable to find an instance of the CRM components. Thanks for your help. I fixed this by ensuring in internet options, security for trusted sites (includes crm server) automatically authenticate with current username/password. Using IE went to crm server, auth...

Finding/matching custom color in publisher
I am trying to find out what the RGB composition is of a custom color background. I created the color earlier. When I reopen the file it resets to the default palette and it does not include my custom color. How do I find out the RGB of a previously created background color. Download this small utility, it is a color picker, free. http://www.nattyware.com/pixie.html -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "JohnnyP" <JohnnyP@discussions.microsoft.com> wrote in message news:2A8BDFFC-66C8-4D85-8629-43F72DCB4...

Entering serial number on a return
When attempting to enter a particular serial number on a return we're getting a message that "the serial number has already been entered for the selected item". Per the KB articles on this message I've checked serial lot trace to confirm that the serial number is not included on any un-posted transaction. I've also confirmed in SOP10201 that it only appears once on an invoice that has been posted, and that it is not in IV00200 at all. Any ideas on what else could trigger this message? -- Jim@TurboChef If I read this right you said it appeared in the SOP10201 t...

Decimal numbers to time format.
Hi there, I'm sure this is a simple one for you guys but I'm not having fun trying to get to the bottom of it. I have two cells: A1 - contains 47 (formatted as 'number to two decimal places') B1 - contains 12 (formatted as 'number to two decimal places') I have other cells formatted for time as [hh]:mm:ss How can I fill C1 with 00:47:12 (formatted as [hh]:mm:ss) - with the 47 and 12 being pulled from A1 & B1 Cheers Lee On Tue, 18 Dec 2007 12:21:54 -0000, "Lee Grant" <lee@inspirationcomputers.com> wrote: >Hi there, > >I'm sur...

Page Numbers
I have a document and I'm using page numbers in the format of Page 1 of 1. I want to print several copies of the same document, but I want the page number to go up for example Page 1 of 2, etc. Can this be done? On 10 Dec 2003 07:31:10 -0800, jobe.gates@akrn.com (Gates) wrote: >I have a document and I'm using page numbers in the format of Page 1 >of 1. I want to print several copies of the same document, but I want >the page number to go up for example Page 1 of 2, etc. Can this be >done? Custom footer "&[Page] of &[Pages]" - without the quotes? --...

Find and replace values across multiple fields
I would like to find and replace several values in my Access table.For example, I would like to change all "%40" to "@", but rather thanspecify a single field/column of my table to search through, I wouldlike the code to search through all of the fields in my table (thereare too many replacements and too many field names to specify).How can I modify the code below so that it searches through each fieldin the table?Public Function RunCleanValues() DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =Replace([fieldname],'%40','@');"End FunctionThank yo...

Returns Mgmt Do Not Require Invoice Item Number for RMA Types.
Do not require invoice item number (or at least make it clear it is required on the screen). We want to enable zero dollar invoices for even exchanges, but don't want to create a bunch of zero dollar invoices for nothing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message...