Forum Discussion
f.gotelli104
Oct 28, 2018Copper Contributor
EXCEL
Hi staff,
I had a problem with some formulas on Excel.
How can I calculate the frequency of a number in a specific cell in various sheets of a same table?
(for example the frequency of number 2 in cell B2 from sheet 1 to sheet 133)
Please help me, it's for my degree!
Regards,
Francesca
P.S. sorry but my English is not my mother tongue
- Haytham AmairahSilver Contributor
Hi Francesca,
In order to calculate the frequency (number of occurrences) of that number, you have to create a list in one of these sheets or in a separate sheet, and then bring all the numbers from all sheets to this list using a formula so that all of them you have in one place, in one column.
let's say that the sheets are named this way:
Sheet1, Sheet2, ... Sheet133
If so, please use the below formula in a separate sheet, and drag it down until you return them all.
=TRIM(INDIRECT("Sheet"&ROW(B1)&"!B2"))
After that, use this formula to get the frequency:
=SUMPRODUCT(LEN(B1:B3))-SUMPRODUCT(LEN(SUBSTITUTE(B1:B3,"2","")))
Please find an example of this in the attached file.
Hope that helps
- Lorenzo KimBronze Contributortry this:
Sub CountOccurrenceOf2inB2()
Dim I As Integer
mcnt = 0
For I = 1 To 133
If Worksheets(I).Range("B2") = 2 Then
mcnt = mcnt + 1
End If
Next I
MsgBox "Occurrence for 2 in Cell B2 = " & mcnt
End Sub
HTH