Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
Freddie_deBoer

Can't Set Histogram Bins Correctly in Excel 2016

Recommended Posts

Posted · Original PosterOP

Hey guys, I have a persistent problem with Microsoft Excel histogram that drives me crazy. I will have a row of data that represents a row of choices from a survey. (In the attached example I have marked them 1-5 for Very Unsatisfied to Very Satisfied, sometimes I do Strongly Disagree to Strongly Agree, etc.) When I use the histogram function in Excel 2016, the program never sets the bins correctly, and trying to set them manually doesn't work either. I want bins of 1, 2, 3, 4, 5, and for the bars to represent the counts of each. But Excel will automatically give me numbers like 2 - 2.74, 2.74 - 3.48, etc. I try to set bin width to 1.0, and it only gives me three thick bins labeled (2,3), (3,4), and (4,5). I try to set the number of bins to 5 and it gives me bins from 2 to 2.26, from 2.26 to 3.2, etc. See attached images. I'm pulling my hair out and would love advice.

histogram.PNG

data.PNG

Link to post
Share on other sites

It's doing this because it thinks each row is a seperate point with a value of whatever is in the cell. You can use...

=countif(range,criteria)

...where range is the data range and criteria is the value you are counting (ex. 2). Make 5 cells of countif functions for values 1-5. Then instead of using a histogram, create a 2-D graph of the 5 cells.

 

Example:

Spoiler

299075772_BinsExample.PNG.ee0c2d6aaeef843c8713aefb05d31560.PNG

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×