# [EXCEL] Using the Frequency function in a table

#### leomac

##### New Member
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.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Fluff

##### MrExcel MVP, Moderator
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
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
What version of Excel are you using?
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

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

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

#### Peter_SSs

##### MrExcel MVP, Moderator

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
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
D3:D26D3=D2+C\$2

#### leomac

##### New Member
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

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

Reminder:

Replies
5
Views
68
Replies
13
Views
135
Replies
1
Views
101
Replies
2
Views
66
Replies
1
Views
52

1,129,820
Messages
5,638,529
Members
417,032
Latest member
Qasdrf

### 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.

### Which adblocker are you using?

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

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