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

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.

Answer 4

Ashish:

Thanks for the advice. So I can't customize the histogram FUNction? I need to do this on my own? That's what I'm asking here, I myself could always write a code for it, but Microsoft doesn't allow for endpoints to be customized? What fields use histograms in the Excel setup? Because in Statistics it is done left point included right excluded.

Answer 5

No. I want to see how this is done using the histogram FUNction. I could write a macro if I wanted to make my own.

In Statistics, it is done left endpoint included, right excluded. What other fields do it the other way?