I am trying to create a bar chart for say some data, say for example
1 1 2 2 3 4 5
I want to create a bar graph so each bar shows the frequency of a range. For example, the frequency of a value in my data set that is between 1 and 3, is 5. So there would be a bar that goes up to 5 on the y-axis. The next bar would go up to 2 on the y-axis since the range would be from 4 to 5.
How would I do this in Excel 2010?
Best Answer
NOTE: This question is vague in the sense of exactly what you are dealing with, but there is enough information here to answer the question. You will have to alter things in this answer to fit your exact scenario.
The easiest way may be to create some helper cells to count the occurrences which fall into the groups you have outlined. Then from those results you can create a chart which has the bars as you described.
In the example below, I used the data you described with the
COUNTIFS
function (click the link to read more about the function).To do this, choose cells outside your working data table. Then enter the formula so it checks the range of your data. I added the data to
A1
thruG1
. Then I usedA3
andB3
for labels while the formula goes in the cells directly below. InA4
the formula counts numbers greater or equal to 1 and also numbers less than or equal to 3. You will notice the range must be given for each criteria.Using the same format, the formula was placed in
B4
to count numbers between 4 and 5 is as follows;With the numbers counted, select
A3
thruB4
and choose to insert a chart of your choice from the Insert tab.