|
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
how to make a bar chart
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 |
| All times are GMT +1. The time now is 09:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com