Histogram endpoints in Excel

Question

How do you change the endpoint inclusion for bins in histograms in Microsoft Excel?

I want 71 to fall into the second bin, not the first. I want 82 to fall into the third bin, not the second, and so on. In other words, Excel is defaulting at (left,right] and I want to change it to [left,right).

I'm a statistics teacher, and this is how we calculate frequency. Any ideas?

Thanks,

Tom

Answer 1

Yes my expected results are in that order. And yes, I agree that it seems configurable. I don't see any information in this link to solve this issue, though. Can you point me to a specific location? Snip it and paste the image? I want to include the left endpoint and exclude the right.

In 2013 here’s what I’ve done (you need to adapt this to your version):-

 - using your data in cells A2:A25

 - and the bins in B2:B6 set as follows 70, 81, 92, 103, 114

1.

Data tab

Analysis group

Data Analysis

Histogram

OK

2.

In the window called:-

Histogram

 - that opens set the following fields as follows:-

Input Range: $A$2:$A$25

Bin Range: $B$2:$B$6

OK

 - to give (see SCREEN SHOT 1).

Chart appears to have been improved in 2016 version. 

Answer 2

Hello Tommunation

I am V. Arya, Independent Advisor, to work with you on this issue. Excel logic includes this in first bin only. This is not customizable.

Answer 3

How do you change the endpoint inclusion for bins in histograms in Microsoft Excel?

I want 71 to fall into the second bin, not the first. I want 82 to fall into the third bin, not the second, and so on. In other words, Excel is defaulting at (left,right] and I want to change it to [left,right).

I'm a statistics teacher, and this is how we calculate frequency. Any ideas?

Thanks,

Tom

You've got 6 bins.

Are your expected results in bin number order?:-

6

6

6

3

0

3

See here for further information:-

https://support.office.com/en-us/article/Create-a-histogram-85680173-064b-4024-b39d-80f17ff2f4e8

I reckon this is configurable (it certainly is in the 2013 version).

Answer 4

Hi,

One can do so with the COUNTIFS() function.  Create the lower and upper limits in 2 columns and then use the COUNTIFS() function.

Answer 5

We'd be interested to know if you managed to resolve your problem.