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

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

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

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

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

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

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

Carlo wrote:
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

hi roohbir

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

then it should work!!

Cheers Carlo

"roohbir" wrote:

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

Carlo wrote:
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

sorry roohbir

it's ctrl + SHIFT + enter!!!!!!

Carlo

"Carlo" wrote:

hi roohbir

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

then it should work!!

Cheers Carlo

"roohbir" wrote:

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

Carlo wrote:
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

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


Carlo wrote:
sorry roohbir

it's ctrl + SHIFT + enter!!!!!!

Carlo

"Carlo" wrote:

hi roohbir

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

then it should work!!

Cheers Carlo

"roohbir" wrote:

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

Carlo wrote:
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