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?

    28 Comments

    Original Poster

    http://i53.tinypic.com/dmu041.jpg

    Its too early !!

    Wha????

    Think you need to be more specific!

    Can you explain exactly what it is you are trying to acheive, as not sure what you are looking to do - cheers

    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.

    Original Poster

    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.

    MarzBarz

    Basically the 100 numbers are shown on top which i want to use. Next i … 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.

    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

    My bad its 99 !!!. Sorry.

    MarzBarz

    My bad its 99 !!!. Sorry.



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

    BFN,

    fp.

    Original Poster

    http://i55.tinypic.com/jgm9f8.jpg

    What formula are you putting in C14?

    Original Poster

    =FREQUENCY(A1:J10,A14:A23)

    Try
    10-19
    20-29
    30-39
    etc

    or else you may be double counting some of the numbers

    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

    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
    Copy cell [C14] down the rest of column C to correspond to the entries in columns A & B.

    BFN,

    fp.

    Original Poster

    cannyscot

    Try 10-1920-2930-39etcor else you may be double counting some of the … Try 10-1920-2930-39etcor 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 … 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.

    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

    Original Poster

    FP it found the first line then went all crazy


    3
    17
    31
    37
    49
    29
    17
    10
    2
    0

    Original Poster

    boydent999

    Apologies, tbh i didn't read the whole thread and didn't know it had to … 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.

    =COUNTIF(A1:J10,"<="&B14)-COUNTIF(A1:J10,"<"&A14)

    does that work?

    Original Poster

    mug51

    =COUNTIF(A1:J10,"



    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.

    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.

    Original Poster

    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

    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" 10th Nov 2010

    MarzBarz

    Tbh best thing is ill wait till the evening for you to come home. Then … 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.

    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" 11th Nov 2010

    PS. I am still waiting for a link to your MS-Excel file (as we discussed by "Private Message" four days ago).

    BFN,

    fp.
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Amazon Italy Galaxy S8 UK12
      2. Argos 3 for 2 reserving early44
      3. DashCam Recommendation With Front And Rear Camera Built In22
      4. £1500 55 inch 4K TV56

      See more discussions