Microsoft Excel

=countif function?

Repeated complete number or digit?

Complete number

The countif function will give you how many times a number is in the array, however you have to type that predetermined number into the function formula for the countif to look for it. The function you seek is MODE. If your array is in B1:B20, then the function formula is =MODE(B1:B20) and the result will be the one number that appears most often. Then, set up another MODE function inside a formula that says NOT (the previous MODE result) and so on. It's been a long time since I did this and I am going from old memory, I didn't go into Excel and try this - so be kind if I missed a step!

that sounds like what I need Master Skywalker, but I'm fuzzy on the second part, how do you tell it "Not the previous MODE result." ???

a) Run the MODE function on array in first column, the answer is in second column, the same all the way down all rows, number 2 appears most often, b) run a simple IF array minus MODE result equals zero then return X, =IF(F5<>0,D5,"X") if it results in some other number then display number, c) run MODE on resultant column which now is void of first results. Might be easier way but this works!!!

that worked for me. I then went in and colored all the numbers white so they are not seen and put the MODE functions at the top in a header format.

Now it works well, looks cool and no one knows I'm using so much of the spreadsheet.

Thanks

I like the color font idea - good addition. Glad to have helped!!!

Matt

There is an array function called {Frequency} in the Open Office clone of Excel. It counts the frequency of data elements that fit into defined bins and outputs the result into an array. This may (or may not) work for you.

The Frequency function also exists in Excel, and was designed to do exactly what the OP is looking to accomplish. Well-documented in Excel Help...

if your file is not confidential, upload it here i'll write a small vba code for you

a million years ago, I could have written the formula. but =mode(a1:a60000) would return the most frequently appearing number. Then use the property of parentheses to generate the not operators. =mode(a1:a60000)!(=mode(a1:a60000)) then =mode(a1:a60000)!(=mode(a1:a60000)!(=mode(a1:a60000))) or just get the mode, do a search and replace that replaces the mode with a random string beginning with a predictable string like NOTFIRSTMODEoeiuweoweiueworiuoiueteoriutqpewiurf (the lowercase part will require you to write a fun little random string generator), then get the mode again and a search and replace with NOTSECONDMODEoeiuweoweiueworiuoiueteoriutqpewiurf then do the mode a third time. Two search and replaces replacing cells containing NOTFIRSTMODE and NOTSECONDMODE with the first and second mode. Slower? More complicated? You bet! Why get two things done in a day when you can get one thing done very slowly in a couple of days? By working unproductively, you ensure that your friends won't get laid off because fo your efficiency.

Hi ,

I could tell you the most repeated number in an array and its frequency by the following formula . Press CTRL+SHIFT+ENTER to get the value in the cell .Try to dig out the second repeated number by modify this formula.

For ex computing values from b4 to b13.

INDEX(B4:B13,MATCH(MAX(FREQUENCY(B4:B13,B4:B13)),COUNTIF(B4:B13,B4:B13),0)) & "and "&MAX(FREQUENCY(B4:B13,B4:B13))

Regards

Earnest.

This is a quick and dirty method. Insert a column next to your array column for the countif function. If the array is in C10:C20, then the formula for D10 is =COUNTIF($C$10:$C$20,C10). Copy this to C11:C20. Then sort C10:D20 on column D. Peter Harris

This did NOT do what I wanted it to do. This counted the number of times a number showed up in the array. My problem is I don't know which number shows up the most. (I have 59 different cutters being used on various jobs and I don't know what is used when.)

Thanks for the effort.. I know you took time out of your day and played with it to get it to work...

Laby

Sorry, but it works for me. The number which shows up 4 times happens more often than the number which shows up 3 times, and the sorting process puts into order the ones that happen most. If two or more numbers have the same frequency you can tidy it with a secondary sort on the number. Regards, Peter

If the number of items in the array is small enough to count by hand, just sort the column and all the like numbers will be together...then count them.

Probably too simple an answer but it's what came to mind!

PIVOT TABLE