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?

4
  • I also tried asking on Quora, to no avail.
    – ions me
    Commented Jan 12, 2020 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
    Commented Sep 25, 2020 at 2:55
  • 2
    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 ...
    – ions me
    Commented Sep 25, 2020 at 5:31
  • I have the same issue. X axis shows days as a decimal number, I think Y axis shows a count formatted as a time?
    – yoyo
    Commented Sep 4, 2021 at 23:05

2 Answers 2

2

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))

(Edit: or use FREQUENCY -- see Travis's answer)

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

1
  • Nice workaround, but yuck, come on google!
    – yoyo
    Commented Sep 4, 2021 at 23:06
1

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 and acknowledge you have read our privacy policy.

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