Excel 2003 Charting - X-axis Values across range

IamMrB

New Member
Joined
Nov 29, 2010
Messages
11
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.

ξ
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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 :(
 
Last edited:
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top