[EXCEL] Using the Frequency function in a table

[leomac]

Hello everyone,
I have a problem with the "FREQUENCY" function. When I use it with the array in a table in excel the function does not give the correct values. It always provides only the first correct value in the entire table. I do not know why.
It should give values like those of this sheet in google instead in excel it gives them wrong.
Below is the link to the google spreadsheet with the data and the image of what I get on excel.

Data

Foglio1 Data,TOT Data,# Categories,# Class,Frequencies 192,25,5,197,6 192,202,5 194,207,8 194,212,4 194,217,2 194,222,0 198,227,0 198,232,0 198,237,0 198,242,0 200,247,0 205,252,0 205,257,0 205,262,0 205,267,0 206,272,0 206,277,0 206,282,0 206,287,0 210,292,0 211,297,0 211,302,0 211,307,0 213,312,0

docs.google.com

Thanks.

 

[Fluff]

If you want to use that in a structured table, try

Data.xlsx
	A	B	C	D	E
1	Data	TOT Data	# Categories	# Class	Frequencies
2	192	25	5	197	6
3	192			202	5
4	194			207	8
5	194			212	4
6	194			217	2
7	194			222	0
8	198			227	0
9	198			232	0
10	198			237	0
11	198			242	0
12	200			247	0
13	205			252	0
14	205			257	0
15	205			262	0
16	205			267	0
17	206			272	0
18	206			277	0
19	206			282	0
20	206			287	0
21	210			292	0
22	211			297	0
23	211			302	0
24	211			307	0
25	213			312	0
26	215			317	0
Foglio1

Cell Formulas
Range	Formula
B2	B2	=COUNT(A2:A26)
C2	C2	=SQRT(B2)
D2	D2	=C2+A2
E2:E26	E2	=FREQUENCY(IF([Data]>N(D1),[Data]),D2:D26)
D3:D26	D3	=D2+C$2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[leomac]

Hello and thanks for the answer. Unfortunately it doesn't work.
To the formula in E2
= FREQUENCY (IF ([Data]> N (D1), [Data]), D2: D26)
I just added the comma after [Data]
= FREQUENCY (IF ([Data]> N (D1), [Data],), D2: D26)
otherwise it gives me error. All with CRTL + SHIFT + ENTER.

Now as values it gives me more or less the sums of the frequencies:
6, 11 (5+6), 15, 20, 25, 25 remaining on 25 until the end. I have no idea why you have all the correct values. I use the table format, which I cannot do without it.
I think this kind of error depends of the table that does not allow the array to function well. In fact, using the simple formula
= FREQUENCY(A2:A24, D2: D26)
on a simple grid, it gives me the correct results. I've no idea why it happens. Do you have other suggestions?

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this.

Now as values it gives me more or less the sums of the frequencies:

That's because you added the comma to formula.

 

[leomac]

Microsoft office professional plus 2019 excel MSO 16.0.13328.20350 64bit italian version.
If I don't add the comma it gives me all zero value with CRTL + SHIFT + ENTER. Without CRTL + SHIFT + ENTER it gives me error.

 

[leomac]

I've update now excel but nothing happens, same problem.

 

[Peter_SSs]

Microsoft office professional plus 2019

Please include that in your account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

See if this works for you (check table name matches)

leomac.xlsx
	A	B	C	D	E
1	Data	TOT Data	# Categories	# Class	Frequencies
2	192	25	5	197	6
3	192			202	5
4	194			207	8
5	194			212	4
6	194			217	2
7	194			222	0
8	198			227	0
9	198			232	0
10	198			237	0
11	198			242	0
12	200			247	0
13	205			252	0
14	205			257	0
15	205			262	0
16	205			267	0
17	206			272	0
18	206			277	0
19	206			282	0
20	206			287	0
21	210			292	0
22	211			297	0
23	211			302	0
24	211			307	0
25	213			312	0
26	215			317	0
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNT(A2:A26)
C2	C2	=SQRT(B2)
D2	D2	=C2+A2
E2:E26	E2	=INDEX(FREQUENCY([Data],['# Class]),ROW()-ROW(Table1[#Headers]))
D3:D26	D3	=D2+C$2

 

[leomac]

Thanks, it works. I would've never figured it out without you. It is also difficult to understand it for me. Thanks again.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Reminder:

Please include that in your account details ....