Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
lovely_pink pe Simpatie
 | Femeie
25 ani
Bucuresti
cauta Barbat
25 - 48 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
Source:
Assuming that for the interval E2:E55 you want to know which is the most frequent word, in E1 you can write the following formula:
=INDEX(E2:E55;MODE(MATCH(E2:E55;E2:E55;0)))
If you apply just the following formula, you find out the position of the most frequent word in the range you set:
=MODE(MATCH(E2:E55;E2:E55;0))
The INDEX in the first formula only shows the word that is at that position.
The link above says these formulas are array formulas so you enter them with CTRL+SHIFT+ENTER.
For me they worked also without that key combination, just plain ENTER.
More sources:
_______________________________________
|
|
| pus acum 6 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
If there are empty cells in the range, you use the following ARRAY formula:
=INDEX(B5:F5;MODE(IF(B5:F5<>"";MATCH(B5:F5;B5:F5;0))))
If you want to use multiple rows / columns, you must use VBA code. See my post below.
_______________________________________
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
If the above won't work, like they haven't worked for me in one case with multiple rows and columns, I used VBA:
Sub FindFrequency()
Dim Rng As Range
Dim WorkRng As Range
Set dic = CreateObject("scripting.dictionary")
On Error Resume Next
xTitleId = "Select range to count the most frequent word"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xMax = 0
xOutValue = ""
For Each Rng In WorkRng
xValue = Rng.Value
If xValue <> "" Then
dic(xValue) = dic(xValue) + 1
xCount = dic(xValue)
If xCount > xMax Then
xMax = xCount
xOutValue = xValue
End If
End If
Next
MsgBox "The most common value is: " & xOutValue & ". It appeared " & xMax & " Times"
End Sub |
Source:
_______________________________________
|
|
| pus acum 5 ani |
|