Can't get Bar chart to use my numbers

[tshad]

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

 

[OssieMac]

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

 

[OssieMac]

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

 

[Jon Peltier]

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

 

[tshad]

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 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

$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

 

[tshad]

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

 

[Jon Peltier]

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
_______


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 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

$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