Basically i have 100 numbers ... between 10-100.

10 20

20 30

30 40

40 50

50 60

60 70

70 80

80 90

90 100

so ive made that and now i want to find out the frequency , ive used the formula but basically once you add up all the frequencies its meant to add up to a 100 right? but mine seems to go crazy. I think i forgot to press shift or whatever in the formula.

Does anyone know what im on about?

10 20

20 30

30 40

40 50

50 60

60 70

70 80

80 90

90 100

so ive made that and now i want to find out the frequency , ive used the formula but basically once you add up all the frequencies its meant to add up to a 100 right? but mine seems to go crazy. I think i forgot to press shift or whatever in the formula.

Does anyone know what im on about?

Other Links From
Here:

Options

## All Comments

(28) Jump to unreadPost a commentThink you need to be more specific!

http://www.meadinkent.co.uk/xlfreq.htm

Edited By: greg_68 on Nov 10, 2010 09:24Also not known is where you would like the results to be displayed.

I am guessing, as you do not say explicitly, that you have numbers from 10-100 in 100 cells so that you will have a frequency count greater than 1 for at least one (possibly ten) of those 90 values (in the 100 cells).

With the steps below I have assumed that you have the 100 numbers (with values from 10 to 100) in the cell range [A1:J10]...

Fill a range of cells (say, [A20:A110]) with the numbers 10 to 100 (so that [A20] contains 10, [A21] contains 11, [A22]: 12..., [A109] is 99, & [A110] is set to 100).

Next highlight (select) the entire range [B20:B110].

Type in cell B20 (with the range [B20:B110] still selected) the following (but note the next line *):

=FREQUENCY(A1:J10,A20:A110)

* Then, (...and this is the important part...) instead of simply pressing [ENTER], use [CTRL]+[SHIFT]+[ENTER] to create an "array" of numbers in the cells [B20:B110].

If you have entered the formula correctly you will see this formula in every cell in the range [B20:B110]:

{=FREQUENCY(A1:J10,A20:A110)}

(Note the opening & 'curly' braces).

If you do not see this, select the contents of cells [B10:B110], clear the contents, & then enter the "array" formula again.

All being well, the cells [B20:B110] will now display the frequency count of each value in the corresponding row in column A.

That is, as cell [A20] reads 10, cell [B20] will be the number of times the value of 10 is in the range [A1:J10].

[A21] is 11; [B21] is the number of times 11 is in the values in cells [A1:J10].

& so on.

BFN,

fp.

You have three values in the range 10 to 20 (inclusive) in your opening post to this thread.

However, you do not have 100 values listed; so it is difficult to tell how your data is presented or the exact content.

As I mentioned, if you advise on where your data is in your worksheet(s) then it will make the answer to your query easier to reach.

BFN,

fp.

What is 99? The number of values you have to find the frequency totals of?

BFN,

fp.

10-19

20-29

30-39

etc

or else you may be double counting some of the numbers

Would be much easier

=SUM(IF($A$1:$J$10>=A14,IF($A1:$J10<B14,1,0),0))

Copy cell [C14] down the rest of column C to correspond to the entries in columns A & B.

BFN,

fp.

10-19

20-29

30-39

etc

or else you may be double counting some of the numbers

Would be much easier

Yeah tried that but same result appeared.

Also i have to use this formula.

hadto be done formula wise.Being blunt, seems daft to not use a pivot table; that's what they're for.

Sorry i can't help more

3

17

31

37

49

29

17

10

2

0

hadto be done formula wise.Being blunt, seems daft to not use a pivot table; that's what they're for.

Sorry i can't help more

its fine ty.

does that work?

does that work?

That worked thank you!!! But i couldnt drag it down i had to manually input each one. Im still gona try and see if i work out how to make the frequency formula work though.

I cannot really debug the issue as I cannot see the screen image you provided previously (due to firewall restrictions on the Corporate network I am using at present).

I have been responding so far based on the text replies from you & everybody else.

Could you elaborate on "went all crazy" please?

Thanks.

BFN,

fp.

PS. mug51's formula could be changed to:

=COUNTIF($A$1:$J$10,"<="&$B14)-COUNTIF($A$1:$J$10,"<"&$A14)

You will then be able to drag this (or copy & paste) down column C.

Are you entering it with the control+shift+enter.

If I do a similar sheet, it works for me.

When you put the formula in column c do you highlight c14:c23 and then put the formula in the formula box or are you putting it just into c14 and dragging down?

Edited By: greg_68 on Nov 10, 2010 14:30No, sorry; I cannot see any of the screen images in this thread at present. All my responses so far have been based on what has been typed above.

PS. For clarity: We do not live together. I will not be "coming" home, I will be going home! :)

However, I believe I have replicated your worksheet as described. Like greg_68 mentioned, are you sure you used [CTRL]+[SHIFT]+[ENTER] to confirm the formula I stated in comment #16?

BFN,

fp.

=FREQUENCY($A$1:$J$10,$B$30:$B$39)

The way Fanpages sugest in comment #6 is the best way to set up the spreadsheet. Obviously substituting the ranges B20:B110 for C14:C23 and A20:A110 to B14:B23 to adapt it to your sheet.

Edited By: trog0 on Nov 11, 2010 04:18: grammarBFN,

fp.