|
Can't get Bar chart to use my numbers
I have a set of numbers:
Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 187,5000 is 2 etc). I am trying to show something like this: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 The scatter graph shows the axis correctly and puts the points in the right place, but the bar graphs don't. Is there a way to make the bar graphs do this? Thanks, Tom |
Can't get Bar chart to use my numbers
Hi tshad,
Format the Level column as text. If you still want the Level data to appear as currency with the dollar sign and 1000's comma, then in another column insert the formula below to convert it to text and then you can Copy-Past Special-Values back over the original data and then delete the column with the text formula. =TEXT(A2,"$0,000") I tested it and it works for me. Regards, OssieMac "tshad" wrote: I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 187,5000 is 2 etc). I am trying to show something like this: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 The scatter graph shows the axis correctly and puts the points in the right place, but the bar graphs don't. Is there a way to make the bar graphs do this? Thanks, Tom |
Can't get Bar chart to use my numbers
Hi again tshad,
I forgot to explain a couple of things before. You will have to right click on your vertical axis and set the following:- Categories in reverse order Horizontal or Y value crosses at maximum category. Now the second one might seem strange setting it to maximum because you want it to cross at $10,000 which is the minimum. However, the reason that Excel thinks it is the maximum is because the values are now text not numbers and Excel sees the last value in the list as the maximum. Hope this makes sense to you. Regards, OssieMac "OssieMac" wrote: Hi tshad, Format the Level column as text. If you still want the Level data to appear as currency with the dollar sign and 1000's comma, then in another column insert the formula below to convert it to text and then you can Copy-Past Special-Values back over the original data and then delete the column with the text formula. =TEXT(A2,"$0,000") I tested it and it works for me. Regards, OssieMac "tshad" wrote: I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 187,5000 is 2 etc). I am trying to show something like this: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 The scatter graph shows the axis correctly and puts the points in the right place, but the bar graphs don't. Is there a way to make the bar graphs do this? Thanks, Tom |
Can't get Bar chart to use my numbers
Delete "Level" from the top left cell, and create your bar chart. The dollar
values form labels on the vertical axis, which is the X axis in a bar chart. The Responses are plotted against the horizontal Y axis. You will have to double click on the X (vertical) axis and check Plot Categories in Reverse Order and Value Axis Crosses at Maximum Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "tshad" wrote in message ... I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 187,5000 is 2 etc). I am trying to show something like this: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 The scatter graph shows the axis correctly and puts the points in the right place, but the bar graphs don't. Is there a way to make the bar graphs do this? Thanks, Tom |
Can't get Bar chart to use my numbers
Hi Ossie,
I am not concerned with how the labels look. What I am concerned with is how the bars look. And also the X axis, which is going 0-12 instead 0 - 3. What I am looking for is: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 What I am getting is: x 11 xx xxxx 9 xxxxx xxxxxx 7 xxxxxxx xxxxxxxxx 5 xxxxxxxxxxxx xxxxxxxxxxxxxxx 3 xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxx $0 $50,000 $100,000 $150,000 $200,000 $250,000 This isn't even close to what I am looking for. I did find out a way to make it work by putting it in 2 rows? 10,000 15,000 18,000 18,750 20,000 21,000 30,000 46,857 50,000 75,000 187,500 250,000 2 1 1 1 1 1 3 1 3 1 1 1 this gives me the following: xxxxxxxx 187,500 xxxxxxxx xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx 20,000 xxxxxxxx xxxxxxxx 18,000 xxxxxxxx xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 This is what I was looking for, but I don't know why I couldn't get it to work using the rows/columns. Also, the Y-Axis doesn't show all the numbers, just every other one. Is there a way to get it to show all the numbers? Thanks, Tom "OssieMac" wrote in message ... Hi tshad, Format the Level column as text. If you still want the Level data to appear as currency with the dollar sign and 1000's comma, then in another column insert the formula below to convert it to text and then you can Copy-Past Special-Values back over the original data and then delete the column with the text formula. =TEXT(A2,"$0,000") I tested it and it works for me. Regards, OssieMac "tshad" wrote: I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 187,5000 is 2 etc). I am trying to show something like this: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 The scatter graph shows the axis correctly and puts the points in the right place, but the bar graphs don't. Is there a way to make the bar graphs do this? Thanks, Tom |
Can't get Bar chart to use my numbers
"OssieMac" wrote in message
... Hi again tshad, I forgot to explain a couple of things before. You will have to right click on your vertical axis and set the following:- Categories in reverse order Horizontal or Y value crosses at maximum category. Now the second one might seem strange setting it to maximum because you want it to cross at $10,000 which is the minimum. However, the reason that Excel thinks it is the maximum is because the values are now text not numbers and Excel sees the last value in the list as the maximum. Actually, I have the Level column set to Currency and the Response Column set to number (it was general before) and it still shows the same thing. Thanks, Tom Hope this makes sense to you. Regards, OssieMac "OssieMac" wrote: Hi tshad, Format the Level column as text. If you still want the Level data to appear as currency with the dollar sign and 1000's comma, then in another column insert the formula below to convert it to text and then you can Copy-Past Special-Values back over the original data and then delete the column with the text formula. =TEXT(A2,"$0,000") I tested it and it works for me. Regards, OssieMac "tshad" wrote: I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 187,5000 is 2 etc). I am trying to show something like this: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 The scatter graph shows the axis correctly and puts the points in the right place, but the bar graphs don't. Is there a way to make the bar graphs do this? Thanks, Tom |
Can't get Bar chart to use my numbers
If you follow my suggestion, and delete the label above the first column of
values, the column that contains the axis labels, your chart will come out correctly with the columnar data. Your "what I am getting" chart is a clustered or stacked bar chart with one series made up of the dollar values, the other of the counts. The magnitudes of the latter series (1, 2, 3) pales in comparison to those of the dollar values, so the series is not apparent in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "tshad" wrote in message ... Hi Ossie, I am not concerned with how the labels look. What I am concerned with is how the bars look. And also the X axis, which is going 0-12 instead 0 - 3. What I am looking for is: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 What I am getting is: x 11 xx xxxx 9 xxxxx xxxxxx 7 xxxxxxx xxxxxxxxx 5 xxxxxxxxxxxx xxxxxxxxxxxxxxx 3 xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxx $0 $50,000 $100,000 $150,000 $200,000 $250,000 This isn't even close to what I am looking for. I did find out a way to make it work by putting it in 2 rows? 10,000 15,000 18,000 18,750 20,000 21,000 30,000 46,857 50,000 75,000 187,500 250,000 2 1 1 1 1 1 3 1 3 1 1 1 this gives me the following: xxxxxxxx 187,500 xxxxxxxx xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx 20,000 xxxxxxxx xxxxxxxx 18,000 xxxxxxxx xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 This is what I was looking for, but I don't know why I couldn't get it to work using the rows/columns. Also, the Y-Axis doesn't show all the numbers, just every other one. Is there a way to get it to show all the numbers? Thanks, Tom "OssieMac" wrote in message ... Hi tshad, Format the Level column as text. If you still want the Level data to appear as currency with the dollar sign and 1000's comma, then in another column insert the formula below to convert it to text and then you can Copy-Past Special-Values back over the original data and then delete the column with the text formula. =TEXT(A2,"$0,000") I tested it and it works for me. Regards, OssieMac "tshad" wrote: I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 187,5000 is 2 etc). I am trying to show something like this: 250,000 xxxxxxxx 187,500 xxxxxxxx 75,000 xxxxxxxx 50,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 46,857 xxxxxxxx 30,000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 21,000 xxxxxxxx 20,000 xxxxxxxx 18,750 xxxxxxxx 18,000 xxxxxxxx 15,000 xxxxxxxx 10,000 xxxxxxxxxxxxxxxxxxxx 1 2 3 The scatter graph shows the axis correctly and puts the points in the right place, but the bar graphs don't. Is there a way to make the bar graphs do this? Thanks, Tom |
| All times are GMT +1. The time now is 07:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com