Is anyone good with statistics and excel here? really simple stuff... - HotUKDeals
Get the HUKD app free at Google Play

#### Search Error

An error occurred when searching, please try again!

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

6y, 3w agoPosted 6 years, 3 weeks ago
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?
6y, 3w agoPosted 6 years, 3 weeks ago
Options

(28)
#1
#2
Its too early !!
#3
Wha????

Think you need to be more specific!
1 Like #4
Can you explain exactly what it is you are trying to acheive, as not sure what you are looking to do - cheers
1 Like #5
Does this help?

Edited By: greg_68 on Nov 10, 2010 09:24
1 Like #6
In order to answer your question properly, I/we need to know which Excel cell range the 100 numbers you wish to find the frequency of reside in.

Also 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.
#7
Basically the 100 numbers are shown on top which i want to use. Next i want to find out how many numbers are available between 10 and 20 , in this case i think its 2. But thanks i will try website greg_68 and also your suggestion fan pages.
1 Like #8
MarzBarz
Basically the 100 numbers are shown on top which i want to use. Next i want to find out how many numbers are available between 10 and 20 , in this case i think its 2. But thanks i will try website greg_68 and also your suggestion fan pages.

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.

BFN,

fp.
#9
My bad its 99 !!!. Sorry.
1 Like #10
MarzBarz
My bad its 99 !!!. Sorry.

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

BFN,

fp.
#11
1 Like #12
What formula are you putting in C14?
#13
=FREQUENCY(A1:J10,A14:A23)
2 Likes #14
Try
10-19
20-29
30-39
etc

or else you may be double counting some of the numbers
1 Like #15
If you're looking for how many of something appear in an excel sheet and you want the results in a table, i suggest you use the Pivot Table facility as opposed to any kind of formula.
Would be much easier
1 Like #16
Put this formula in cell C14 using [CTRL]+[SHIFT]+[ENTER] at the end of the input:

=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.
#17
cannyscot
Try
10-19
20-29
30-39
etc

or else you may be double counting some of the numbers

boydent999
If you're looking for how many of something appear in an excel sheet and you want the results in a table, i suggest you use the Pivot Table facility as opposed to any kind of formula.
Would be much easier

Yeah tried that but same result appeared.

Also i have to use this formula.
1 Like #18
Apologies, tbh i didn't read the whole thread and didn't know it had to 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
#19
FP it found the first line then went all crazy

3
17
31
37
49
29
17
10
2
0
#20
boydent999
Apologies, tbh i didn't read the whole thread and didn't know it had to 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.
1 Like #21
=COUNTIF(A1:J10,"<="&B14)-COUNTIF(A1:J10,"<"&A14)

does that work?
#22
mug51
=COUNTIF(A1:J10,"<="&B14)-COUNTIF(A1:J10,"<"&A14)

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.
#23
MarzBarz
FP it found the first line then went all crazy... 31731374929171020

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.
#24
Tbh best thing is ill wait till the evening for you to come home. Then you can view the screenshots. I assumed you could see the previous screenshots
#25
MarzBarz
=FREQUENCY(A1:J10,A14:A23)

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:30
1 Like #26
MarzBarz
Tbh best thing is ill wait till the evening for you to come home. Then you can view the screenshots. I assumed you could see the previous screenshots

No, 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.
#27
I've replicated your error and it looks like instead of preselecting the output range you have input the formula at c14 and copy and pasted it down. Doing this also shifts the selected ranges down. Fanpages had the right idea in that your formula needs some absolute locations.use this formula and it should work.

=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: grammar
#28
PS. I am still waiting for a link to your MS-Excel file (as we discussed by "Private Message" four days ago).

BFN,

fp.