Count Most Freqently Occuring Number(s) In A Cell
Apr 26, 2007
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
View 9 Replies
Dec 4, 2006
I need a probability distribution for amounts to be shipped taking into account both the probabilty distribution on parcel sizes (amounts to be shipped) per ship as well as the probabilities on the number of ships arriving.
Please see the following:
Say you have a probabilty distribution for ships arriving, i.e. the probabilty of 1 ship arriving in a specific time period= 0.25 ( event a), the probabilty of 2 ships arriving = 0.2 (event B) etc. Please take into account that this can go up to about 20 ships per time period.
Each ship can have one of four parcel sizes, (demand that needs to be met) (the following are just example probabilties)
0 tons with a probability of 0.67 event 1
1000 tons with a probabilty of 0.08 event 2
1500 tons with a probabilty of 0.222 event 3
2000 tons with a probabilty of 0.022 event 4
Thus event a has a probabilty of occuring, in event a, events 1to 4 can occur with their respective probabilties.
I assumed events 1 to 4 stay the same for all ships arriving (as we are dealing with multiple products, each product will have different parcel size distributions, however I look at products seperately, thus only one product is considered here)
This is simple for 1 or 2 ships, but as the number of ships increase the number of combinations become excessively large.
Should i use a macro that determines permutations/combinations? I am kind of clueless on this one.
If it helps to put the problem in context; I am trying to determine a demand distribution as input for a stochastic programming model
View 7 Replies
View Related
Feb 14, 2007
1. First thing I am trying to do. I have a column of cells that have multiple values, some with text and some with no values at all. I want to be able to display in A1 the most commonly occurring text in cells C1:C15, and be able to display in B1 the number of times that A1 occurs in the same range. Below are the formulas that I am using. There are two problems that I am running into: First, the formula returns a #NA error if any of the cells in the range are left blank. Second, the formula counts the spaces or zeros, so if there are more blanks than the word amber then A1 returns and B1 returns the corresponding number.
A1
=INDEX(C1:C15,(MODE(MATCH(C1:C15,C1:C15,0))))
B1
=COUNTIF(C1:C16,A1)
2. Second thing I am trying to do. In A2 I want to display the second most commonly occurring text in the range, with its corresponding count in cell B2, and the third most in A3 and B3, etc
Illustration:
C1 Amber
C2 Red
C3
C4
C5
C6 Red
Desired result:
A1 "Red" B1 "2"
A2 "Amber" B2 "1"
Results with forumla as posted
A1 " " B1 "3"
View 10 Replies
View Related
