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
"roohbir" wrote:
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 he
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
Carlo wrote:
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$20C1)*($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
"roohbir" wrote:
it didn't have what i am looking for mate.
anything else?
cheers
Vince wrote:
roohbir wrote:
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
try here http://www.peltiertech.com/Excel/Cha...ml#hdrTutorial