+ Reply to Thread
Results 1 to 2 of 2

making histogram from data vs time

  1. #1
    Registered User
    Join Date
    05-14-2016
    Location
    Netherlands
    MS-Off Ver
    office 2016
    Posts
    6

    making histogram from data vs time

    Hi,

    I have this data set where a value (column A) is assigned a fixed time (column B).
    the min for the value is -27.49, the maximum is 16.14.
    for the time is the minimal 30 minutes, and the maximum several hours (in steps of 30 minutes)

    I want to view all these values according to the time they are assigned in a histogram.
    So I want a histogram with bins of 30 minutes where:
    bin1: 15-45 minutes
    bin2: 45-75 minutes
    etc. etc.

    the data type of column A is general, of column B is time (hours:minutes:seconds)

    how can I do this in excel 2016, because when I try to use histogram I get a blanc graph.

    Thank you in advance!




    part of the DATA

    -27.49 08:30:00
    -21.6 15:00:00
    -15.93 06:00:00
    -14.98 09:30:00
    -14.66 08:30:00
    -14.12 13:00:00
    -12.34 07:00:00
    -12.33 10:00:00
    -11.6 14:00:00
    -11.42 12:00:00
    -9.81 07:00:00
    -9.64 07:30:00
    -9.11 06:30:00
    -9.02 07:30:00
    0.97 00:30:00
    0.97 02:00:00
    0.97 02:30:00
    0.97 01:30:00
    0.97 05:00:00
    0.97 00:30:00
    0.98 01:00:00
    0.98 02:00:00
    0.98 01:00:00
    0.99 03:30:00
    0.99 01:30:00
    0.99 01:30:00
    5.56 01:30:00
    5.65 00:30:00
    5.83 12:00:00
    6.7 04:00:00
    6.79 01:00:00
    7.11 01:30:00
    7.31 00:30:00
    10.35 09:30:00
    12.77 01:00:00
    13.15 02:00:00
    16.14 01:30:00

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

    Re: making histogram from data vs time

    It is not clear to me exactly what you want in the graph.

    Creating a histogram makes me think of couting "how many entries have time stamps in each bin" so that column A is not even considered in building the histogram. Is that correct? Assuming that is correct, I am surprised that you had trouble with the histogram tool, because it should have done just fine with that task (unless your "bin" numbers were completely different from you "time stamps"). Since I would rarely use the histogram tool, here's how I would build this histogram (I'm still not sure this is exactly what you want).

    1) Enter "bins" data in a column.
    1a) Enter 0:15:00 in row 1 of bins column (column E for example).
    1b) Enter =E1+30/24/60 and copy fill down for as many bins as needed. If you are unfamiliar with how Excel stores dates/times, review something like this: http://www.cpearson.com/Excel/datetime.htm#SerialDates
    2) With the bins array in place, enter the FREQUENCY() function in the adjacent column https://support.office.com/en-us/art...7-fd9ea898fdb9 Note that the FREQUENCY() function is an array function and must be entered accordingly.
    2a) If you have trouble with the FREQUENCY() function, a series of COUNTIFS() functions will accomplish the same purpose.
    3) Insert Column chart using the bins array as the horizontal category axis data and the FREQUENCY() column as the values/series data.

    If I understand what you are looking for, that should do it. If I do not understand, the basic idea should still work. Figure out in step 1 what you want for the "horizonatal" x axis data and enter that into a column, then figure out what you want for the vertical y axis data and enter that into an adjacent column. Then use those two columns to create a column chart.
    Where do you get stuck?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Need help making a histogram
    By Spookycolt in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-03-2014, 01:23 PM
  2. [SOLVED] I was creating a histogram; now i have data but no histogram.
    By ShannonMills3 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-13-2006, 07:10 PM
  3. [SOLVED] How to Histogram w/o raw data but histogram Table on Excel
    By 2005 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-01-2006, 02:50 AM
  4. [SOLVED] How to make excel not round real numbers when making a histogram?
    By Leedawg in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-21-2005, 03:05 PM
  5. histogram for time values
    By Biff in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 12:05 PM
  6. histogram for time values
    By in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  7. histogram for time values
    By in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

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