Excel 2003 Charting - X-axis Values across range

[IamMrB]

Hello,
I am working with the following table:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=64>Probability</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Value</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num="0.2">20.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num="0.65">65.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>250</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 align=right x:num="0.15">15.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>500</TD></TR></TBODY></TABLE>

I have the value on the X-axis, and want to show a column graph with the height of the column lining up with the probability value on the y-axis.
I also want to have a range of values on the X-axis, say from 50 to 550 spaced at increments of 50.
Thus, my x-axis has values of 50,100,150,200,250, etc. My y-axis has values of 20, 40, 60, 80, 100 say.
Can you please help me accomplish what I'm trying to do?
Many Thanks

 

[xenou]

Use an x-y scatter graph with your data. the values for 20%, 40%, etc on the y axis and 50, 100, etc. on the x-axis will be the major tick line markers, but the graph will be whatever your data values are.

If you want columns lining up with the 50, 100, 150 marks but your data is not actually those values, then you may need to create pooled values around these figures - more like a frequency population or a histogram. Or perhaps just rounding the source data to the closest "50" (in these alternatives you might not need the XY chart).

Hope this gives you some ideas. Otherwise, post more sample data that you will actually be working with. Your original numbers seem too simplified when compared against your description that follows.

ξ

 

[IamMrB]

Hi Xenou,
Thank you for the suggestion. In fact, what I'm trying to do is somewhat like a frequency distribution, but with discrete figures. I'll try the x-y scatter plot to see how that works.

 

[IamMrB]

Let me try to clarify my need a bit more. I'm not sure if I can, but will try.
Remember in the days before Excel, one would create a chart with values along the x and y axis. Then, one would plot their data on the chart. So, I'd get my x-axis value, go to that 'place' on the x-axis, and then create my column to be the height of the y-value.
Well, that's what I want to do, but I don't know how to do this in Excel.

My range of values for x will go from 50 to 500, and I'd like to 'bin' these in increments of 50. So my x-axis might look like:

|50|100|150|200|....|500|

My y-axis will be my guestimate of how likely it is that each of my x-values may occur. Thus, my y-values will range from 10-100%

The problem is that I will not have all of the bins of the x-axis filled. In fact, for my first go, I will have 3 values, and then as I refine the 'model' I will add more. In the end I will have a probability distribution where I've assigned the probabilities.

I hope this is helpful to anyone who may be able to help me.
Thank you so much in advance.

 

[xenou]

The best I can come up with is to have all the x-axis values in your data. There won't be anything showing if their is no corresponding y-axis value, but they will still be present on the x-axis this way. I tried finding a way to just label the x-axis tick marks at increments of 50 but I couldn't get it to work using XL 2007 - so I share your frustration. There's some x-axis settings for for "interval between tick marks" and "interval units" but though these seemed promising at first they didn't seem to do anything

 

[IamMrB]

HI,
I just wanted to let you know that I figured out what I wanted to do. Basically, I'm doing a data distribution so I just set 0 values for the x-axis where I had no data, and then the probability values as counts where I did have data. I haven't done it yet, but I believe it will work fine.

Oftentimes, it pays to think about what one is trying to do from a different viewpoint.

 

[Jon Peltier]

Mr B -

A regular column chart, with columns placed at any arbitrary X value, is not really possible in Excel, though you could fake it. A column style chart is probably not appropriate if this is really what you're after.

What you've stumbled across in your last post is probably what is called a histogram. The X axis is divided into ranges, and the columns fit within the ranges showing by their height the number of observations within each range.

Your ranges might be 0-50, 50-100, 100-150, etc. The columns would be formatted to touch each other without any gaps (unless the value of a column is zero, but this gap has a very short column in it).

 

[IamMrB]

Hi Jon,
With a lot of effort, and too much thinking probably, I did learn that I could do what I wanted to do with a PivotChart. In the process I learned how neat PivotCharts are! So all is good.
Unfortunately, I also learned (please tell me if I am wrong) that I cannot have all bars be the same color except for 'coloring' each bar individually.

Thanks for your help.

MrB.

 

[Jon Peltier]

You should be able to get all bars one color. Are the values listed in a column? If not, you need to pivot the table, because a pivot chart only plots by column. If so, check that you didn't somehow choose the option to color differently by point.