+ Reply to Thread
Results 1 to 4 of 4

Histogram: What is the formula to determine the frequency

  1. #1
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Denmark
    MS-Off Ver
    O365
    Posts
    256

    Histogram: What is the formula to determine the frequency

    I am trying to make a histogram template to determine the error reports. This template will allow the user to enter upto 200 data points. I attached the document I am working with. My problem is I do not know the formula to use to automatically determine the frequency (E7:E17) and to automatically show the chart according to the datahistogram _ question.xls

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Histogram: What is the formula to determine the frequency

    The function you want is =FREQUENCY(StatCol,MyBin)
    Where StatCol is the named range of your data
    and MyBin is the named bin range
    ........ The Names make it easier because you're using separate columns.

    THE trick is in how you enter the function........
    1.Select range E7:E17
    2.Enter the formula in the formula bar
    3.CTRL-SHIFT-ENTER

    The answer is :
    109.25 1
    112.19 1
    115.12 6
    118.06 9
    121.00 14
    123.94 17
    126.87 22
    129.81 11
    132.75 11
    135.68 4
    138.62 4
    Elegant Simplicity............. Not Always

  3. #3
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Denmark
    MS-Off Ver
    O365
    Posts
    256

    Re: Histogram: What is the formula to determine the frequency

    Hi, I am not sure what I did wrong.... but it seems that the =frequency is not working for my template. Is it because the datas I have are not just in one column (I used =FREQUENCY(C19:C43+E19:E43+G19:G43+I19:I43+K19:K43+M19:M43+O19:O43+Q19:Q43;C7:C17))... I also tried "," to separate each cell instead of "+" but seems it is also wrong.

    or maybe my bin is also wrong. is there a formula to determine the bin? because I manually created the bin formula (please check the formula I used in column C.

    ** I attached the doc using the formula you suggested. histogram _ question.xls

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Histogram: What is the formula to determine the frequency

    You can't use multiple cell references . that's why i used a named range...
    1.select the first column of data
    2.hold down the CTRL key
    3.select all the other data columns
    4.enter a name in the Name Box (top left) the one that normally displays cell references.

    Then just enter the function using the name - keeps it nice and simple

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1