2

I've tried making a histogram of my data, which looks like so:

12:00:02 PM

3:00:00 PM

12:30:00 PM

12:30:00 PM

2:00:00 PM

3:00:00 AM

…

I highlighted the data and selected insert > chart > histogram.

And got: terrible histogram

The axes labels are completely screwed up:

  1. It's formatted as numbers instead of dates!
  2. Some entries are greater than 1!

I've tried everything I can think of. How do I fix this?

3
  • I also tried asking on Quora, to no avail. – Ion Sme Jan 12 '20 at 2:52
  • Could you please draw what the ideal result would be? For the data example you gave. I could not quite get what kind of histogram you want. Thanks! – nic Sep 25 '20 at 2:55
  • 1
    Same picture, but: The y axis should be numbered 1,2,3,4 ... with the units of "count". The x axis should have 1:00 am, 2:00 am ... – Ion Sme Sep 25 '20 at 5:31
1

Unfortunately there's no nice way to fix this. There are two options I see:

1. Convert time values into numbers (hours or minutes)

You can use TO_PURE_NUMBER(x) to convert a duration to a number, where the value 1 equals 24 hours. So to get hours you can use 24*TO_PURE_NUMBER(x) or for minutes use 60*24*TO_PURE_NUMBER(x), etc.

Then when you create the histogram the y-axis will just be count and the x-axis will have those hours or minutes with a decimal part.

Decimal time labels

2. Build your own histogram using a bar chart

On a separate sheet build a table of bucket boundaries, then use COUNTIFS to count the number of values in each bucket. This can look something like:

=ARRAYFORMULA(COUNTIFS(Sheet1!E$2:E,">="&$A2:$A14,Sheet1!E$2:E,"<"&$A3:$A15))

Where A2:A15 are the bucket boundaries. Then, format the bucket boundaries however you like and put the table into a bar chart.

Bar chart disguised as a histogram

0

I'll just add that you can use the FREQUENCY function instead of COUNTIFS to populate your "buckets." If your buckets are in A2:A14 and your data is on Sheet1!E2:E, try:

=arrayformula(frequency(Sheet1!E2:E,A2:A14))

Then, instead of using the histogram chart, just make a simple column chart from this table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.