Groups

# Is anyone good with statistics and excel here? really simple stuff...

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?

Original Poster

Think you need to be more specific!

meadinkent.co.uk/xlf…htm

Edited by:"greg_68" 10th Nov 2010Also 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.

Original Poster

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.

Original Poster

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

BFN,

fp.

Original Poster

Original Poster

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

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

BFN,

fp.

Original Poster

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

Original Poster

3

17

31

37

49

29

17

10

2

0

Original Poster

its fine ty.

does that work?

Original Poster

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.

Original Poster

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" 10th Nov 2010No, 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" 11th Nov 2010BFN,

fp.