Count most freqently occuring number(s) in a cell

A

andydtaylor

Active Member
Joined
Feb 15, 2007
Messages
360
Office Version
  1. 2016
Hi,

My brain's clearly not fired up today because I'm stuck on the following!

If anyone can help I'd be very grateful.

I'd like to count the most frequently occuring value in a cell. That's it basically.

Say you have the following (actual extract) in a single cell

17,18,58,59,18,59,1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,16,18,23,49,54,59,62,18,59


What formula can I use to show that the most commonly occuring value appearing is 18? [Possible values are 10 through to 99].

The source data for this is in fact a single row accross 5 columns and I concatenated it thinking that made things easier.
The original:

B11: 17,18,58,59
C11: 18,59
D11: 1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50
E11: 16,18,23,49,54,59,62
F11: 18,59


Ideally the formula should take this (B11:F11) range as it's input (I can then spill it down 50-odd rows)

ps I have tried the following

=INDEX(B11:F11,MATCH(MAX(COUNTIF(B11:F11,B11:F11)),COUNTIF(B11:F11,B11:F11),0))

Unfortunately though the internet tells me this should work, the result I get back is 18,59 which is wrong because:

18 occurs 5 times
59 occurs only 4 times

Any insight you can give me is much appreciated,

Andrew
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Thanks for your reply - but I tried that (I think it expects one value per cell) Likewise I've tried frequency but got nowhere. You can see that these numbers are delimited, so I'm aware that I can spill them out over x number of cells and use dynamic ranges to use the mode function - but I'm looking for something a bit neater. If there's no better way, I have that as my 'plan B'

Regards,

Andrew
 
Upvote 0
For one-offs - with my limited knowledge , I'd probably just use the Text to Columns option, as the numbers are delimitted by a comma and this will split the numbers into individual cells (works with all the numbers concatenated) and then use the MODE function to get the most common number.
 
Upvote 0
Hi,

Define MostFrequent

Refers to:

=EVALUATE("{"&Sheet1!$A$1&"}")

change the sheet name and cell

In A2,

=MODE(MostFrequent)

HTH
 
Upvote 0
Hi Krishna,

That's almost a genius solution for a single cell- can you explain to a excel part-timer like me how I can edit this to recognise multiple matches and spill it down many rows?

Re spilling down multiple rows - should I be evaluating a fixed cell and use a macro to past in the numbers from one row, extract the common value and then past in the numbers from the next row?

Re multiple hits - if the formula it finds that say there are two numbers which occur the same number of times it only seems to return the lowest value? I tried adding in an extra '59' in my example above and it didn't return it as a value.

Last question - can you suggest why this formula below doesn't work?

=INDEX(B11:F11,MATCH(MAX(COUNTIF(B11:F11,B11:F11)),COUNTIF(B11:F11,B11:F11),0))


Thanks,


Andrew
 
Upvote 0
Hi,

I did change the above formula.

If your range is B11:F11 then

select B11 and define MostFrequent

Refers to: =EVALUATE("{"&Sheet1!B$11&"}")

In B12 and copied across,

=IF(ISNA(MODE(MostFrequent)),MIN(MostFrequent),MODE(MostFrequent))
Book1
BCDEF
1117,18,58,5918,591,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,5016,18,23,49,54,59,6218,59,59
12171811659
Sheet1


HTH
 
Upvote 0
Thnaks again for your reply

I think this is getting me closer. I'm still a bit concerned as to what happend when there are 2 values which equally occur the most number of times. So if you had

A______B____C______D
1,2,4___2,4__1,2,3,4__2,3,4

2 and 4 would both appear 4 times each, but the approach you've suggested would only return the '2' as the answer.

Any thoughts as to how this could be taken into account?

I'm still puzzled by why my original index/match/max formula didn't work...


Regards,

Andrew
 
Upvote 0
Determine most frequntly occuring value(s) in a cell

H,

Happy Friday to you all! Can anyone help me kill an issue which has had me stumped?

Essentially I'm trying to find the modal value from a range of cells full of space (can be comma) separated numbers. However there tends to be be 1, 2 or 3 values that occur with equal highest frequency. The MS Excel mode function will only capture the lowest value of these equal highest frequency values.

I discoverd at this URL:
http://www.cpearson.com/Excel/lists.htm

The following formula wich in theory gives the most frequently occuring values:
=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

This formula should be perfect for me, but as seen in my example in this thread, this can return inaccurate answers e.g. report that 18 and 59 occur with an equal joing highest frequency, when 18 occurs 5 times and 59 occurs 4 times.

I have no clue where to go from here! Please help!

Thanks,


Andrew
 
Upvote 0
Hello Andrew,

that formula won't look at individual values within the cell but only the entire cell contents so the reason you get 18,59 is because you have 2 cells that contain exactly that, while the other cells are completely different. It has nothing to do with the occurrence of 18 or 59 individually.

I could give you a long formula which will extract the most common number from your data in a single cell but this won't deal with ties, the same way mode won't.

It may be simplest to put all values in individual cells, so if you end up with individual numbers in one column e.g. A1:A100 you can just use MODE formula in D1 to give the most commonly occuring number

=MODE(A1:A100)

then to show any other numbers which occur the same number of times use this formula in D2 copied down as far as necessary

=IF(COUNTIF(A$1:A$100,MODE(IF(A$1:A$100<>"",IF(ISNA(MATCH(A$1:A$100,D$1:D1,0)),A$1:A$100))))=MAX(COUNTIF(A$1:A$100,A$1:A$100)),MODE(IF(A$1:A$100<>"",IF(ISNA(MATCH(A$1:A$100,D$1:D1,0)),A$1:A$100))),"")

confirmed with CTRL+SHIFT+ENTER
 
Upvote 0
You must log in or register to reply here.

Similar threads

ibmy
  • Solved
Count number of occurence of value with condition
2
Replies
10
Views
351
Alex Blakenburg
A
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs
R
  • Locked
  • Question Question
Count the number of times a particular word appears in every nth column
Replies
1
Views
546
Fluff
Fluff
R
  • Solved
Count the number of times a particular word appears in every nth column
2
Replies
12
Views
1K
FormR
FormR
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
337
Fluff
Fluff

Forum statistics

Threads
1,225,725
Messages
6,186,655
Members
453,368
Latest member
xxtanka

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top