+ Reply to Thread
Results 1 to 7 of 7

Help with relative frequency percentages

  1. #1
    Registered User
    Join Date
    08-03-2014
    Location
    adelaide, australia
    MS-Off Ver
    2013
    Posts
    3

    Help with relative frequency percentages

    Hi everyone.

    I've decided to ask for some help after pulling my hair out for a couple of days :/

    I have temperature data listed in column A, and a corresponding sample figure taken at that temperature in column B next door. I need to find a way to sum each sample amount at regular intervals of temperature (i.e. in 0.1 increments). The pivot table only gives increments around temperatures where positive sample numbers occur (i.e. where there is 0 sample, say at 14.2 degrees), the group field will look like this (14 - 14.1; 14.3-14.4... etc), missing the 14.2 interval completely. For the analysis I need to run, I need all temperature group intervals, not just the ones that include positive sample figures.

    Can I force the pivot table to include all 0.1 increments? or is there a function I don't k ow about that might help me achieve this?

    Thanks in advance

    Stu

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,808

    Re: Help with relative frequency percentages

    If I understand your need correctly you could manually create a table, similar to a pivot table, and then use formulas to populate it. SUMIFS will be your friend on this one.

    Without some kind of sample data it's difficult to give you more of a solution than that, but post some data here and I'll show you what I mean.

    BSB.

  3. #3
    Registered User
    Join Date
    08-03-2014
    Location
    adelaide, australia
    MS-Off Ver
    2013
    Posts
    3

    Re: Help with relative frequency percentages

    Hi BSB - thanks ever so much for your reply!

    Yes, I think you might be on the same page as me. The data posted here is a redacted version of my current worksheet. In columns A & B are the data for which I need frequencies / percentages. These Relative percentages will be used to create a quotient analysis later on, but to the issue at hand:

    Column F are the intervals of temperature (0.1 degree increments) for which I'd like to display (a) the sum of eggs/ cubic meter at each interval, and (b) the percentage value of said sum for the entire number of eggs. The pivot table in column P is a demonstration of what I need, however, you can see that temperature intervals are missing (i.e., the first row is 12.3-12.1, and then jumps to 14-14.1 - missing the values in-between). This is due to a lack of data points representing those temperatures - however for the quotient to be an accurate representation, it needs to match the spread of temperature frequency intervals as demonstrated in columns F, G and H.

    The resulting table I hope to achieve would take on the format as displayed in columns K, L, M (highlighted) - with matching temperature intervals.

    Hope this makes some sense. I'm so very out of practice with excel (my undergrad training was quite some time ago!)

    Cheers

    Stu

    Book1.xlsx

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,280

    Re: Help with relative frequency percentages

    It looks to me like you want the FREQUENCY() function: http://office.microsoft.com/en-us/ex...in=HA102752955 Note that this function is an array function and must be entered with cntrl-shift-enter. The result of the frequency function can then be summed to obtain the total, and the percentages would then be simple =(result for that bin)/total.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,808

    Re: Help with relative frequency percentages

    Have a look at columns L & M in the attached. Is this anything near what you're after??
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-03-2014
    Location
    adelaide, australia
    MS-Off Ver
    2013
    Posts
    3

    Re: Help with relative frequency percentages

    Well, thats just marvellous!

    You Sir (Im assuming the gender), are what we'd call here in Oz a bloody legend! This is exactly the formula I needed. Thank you very much. The SUMIFS function is one I'm not familiar with. I can use this in many applications in the future.

    When I'm next in london a beer or two are on me.

    Consider this one solved.

    Thanks again

    Stu

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,808

    Re: Help with relative frequency percentages

    No problem mate. Happy to help.
    Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

    I shall look forward to the beer

    BSB.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to graph a Relative frequency distribution???
    By globulous in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-29-2007, 05:25 AM
  2. [SOLVED] Calculate relative frequency?
    By mlh0654 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2006, 11:15 AM
  3. relative-frequency histogram
    By eschorer in forum Excel General
    Replies: 0
    Last Post: 03-19-2006, 09:15 PM
  4. histograms - frequency and relative frequency?
    By confusedstudent in forum Excel General
    Replies: 2
    Last Post: 02-08-2006, 04:25 AM
  5. [SOLVED] How do I create a Histogram for relative frequency distributions?
    By pipereed in forum Excel General
    Replies: 1
    Last Post: 02-28-2005, 12:06 AM

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