[EXCEL] Using the Frequency function in a table

leomac

New Member
Joined
Aug 21, 2015
Messages
29
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.


Thanks.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,729
Office Version
  1. 365
Platform
  1. Windows
If you want to use that in a structured table, try
Data.xlsx
ABCDE
1DataTOT Data# Categories# ClassFrequencies
21922551976
31922025
41942078
51942124
61942172
71942220
81982270
91982320
101982370
111982420
122002470
132052520
142052570
152052620
162052670
172062720
182062770
192062820
202062870
212102920
222112970
232113020
242113070
252133120
262153170
Foglio1
Cell Formulas
RangeFormula
B2B2=COUNT(A2:A26)
C2C2=SQRT(B2)
D2D2=C2+A2
E2:E26E2=FREQUENCY(IF([Data]>N(D1),[Data]),D2:D26)
D3:D26D3=D2+C$2
Press CTRL+SHIFT+ENTER to enter array formulas.
 

leomac

New Member
Joined
Aug 21, 2015
Messages
29
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,729
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 21, 2015
Messages
29

ADVERTISEMENT

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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’)
1606697157185.png



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

leomac.xlsx
ABCDE
1DataTOT Data# Categories# ClassFrequencies
21922551976
31922025
41942078
51942124
61942172
71942220
81982270
91982320
101982370
111982420
122002470
132052520
142052570
152052620
162052670
172062720
182062770
192062820
202062870
212102920
222112970
232113020
242113070
252133120
262153170
Sheet1
Cell Formulas
RangeFormula
B2B2=COUNT(A2:A26)
C2C2=SQRT(B2)
D2D2=C2+A2
E2:E26E2=INDEX(FREQUENCY([Data],['# Class]),ROW()-ROW(Table1[#Headers]))
D3:D26D3=D2+C$2
 

leomac

New Member
Joined
Aug 21, 2015
Messages
29
Thanks, it works. I would've never figured it out without you. It is also difficult to understand it for me. Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,990
Messages
5,628,001
Members
416,286
Latest member
ko15

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
Top