how to make a bar chart

R

roohbir

I have a column on my excel file that has ages of different people. I
would like to make a bar chart that gives me the no. of persons on the
Y axis, and different age-groups on the X-axis(for example 30-40,
40-50....)
Any help would be appreciated.
Thanks in advance.
Roohbir
 
C

Carlo

Does it bother you if you would have to go on a 2nd Table?
If not, i would do it like that:

Column A: Your Data
Column C: Lowerrange for Age-Group
Column D: Upperrange for Age-Group
Column E: Title for the Group
Column F: Formula for counting

i.e. :
C: 20
D: 30
E: "20 to 30 years"
F: =SUM(($A$1:$A$20>C1)*($A$1:$A$20<=D1))
(Column F is an Array Formula, you have to press
CTRL + SHIFT + ENTER to enter this formula!!!!)

After that you can make your chart with the columns E and F

Hth

Cheers Carlo
 
R

roohbir

Hello Carlo,
I tried what you advised. I get the same result. I am not sure I am
following you correctly though. Hence I am adding the data here:

70 40 50 40-50 1
80 50 60 50-60 1
71 60 69 60-69
71 70 80 70-80
66 80 90 80-90
83
70
77
79
65
62
57
50


You see I get 1 in each case. The data is in the left-most column.
Maybe now you can explain better.
Cheers
Roohbir
 
C

Carlo

Hi roohbir

if you take exactly this data, then there is exactly 1 person in the range
from 40 to 50 -> the one with 50 and exactly 1 from 50 to 60 -> 57!
Try to expand the formula to every of the 5 ranges! then you should get this:

70 40 50 40-50 1
80 50 60 50-60 1
71 60 70 60-70 5
71 70 80 70-80 5
66 80 90 80-90 1
83
70
77
79
65
62
57
50

if you enter 40 and 50 the formula counts the ones with 41 and 50 and those
in between, if you want to change that, you have to change the formula this
way:
=SUM(($A$1:$A$20>=C1)*($A$1:$A$20<=D1))
counts 40, 50 and everything in between or
=SUM(($A$1:$A$20>=C1)*($A$1:$A$20<D1))
counts 40, 49 and everything in between

hth

Cheers Carlo
 
R

roohbir

Hey Carlo,
This didn't really work on my machine. I don't know what I am doing
wrong.
Still getting wrong results.
Do you know of any function that I can use like COUNTIF?
Roohbir
 
C

Carlo

hi roohbir

did you enter the formula with ctrl+alt+enter?
because it's an array-formula!

then it should work!!

Cheers Carlo
 
R

roohbir

thanks for the reply. i just wrote the formula straight into the box.
how should i do ctrl+shift+enter? like where should i write the formula
first?
Thanks
Roohbir
 
Top