Name that Chart!

March 9, 2008 1:49 PM

Excel help: How do I make a chart which displays my values as bars extending above and below a particular line.

I am charting the Exp(B) values of a logistic regression: any number above 1 indicates an increase in the likelihood of belonging to the group represented by the dependent variable and any number below 1 indicates a decrease. I want a line at 1 with numbers above 1 showing as bars extending above the line, and numbers below the line extending downwards. I'm sure it would help if I knew the name of such chart, but if I did, I wouldn't have to AskMe, er, You.

posted by arcticwoman to computers & internet (12 comments total)

I think you're looking for a box chart or box plot.

For the line at 1, just add another series of data points with value of 1 and plot it on a secondary axis. Convert it to a line chart and set it on the same axis min/max so you end up with a straight horizontal line.
posted by junesix at 2:06 PM on March 9, 2008

Nope, not looking for a box plot. Thanks though.
posted by arcticwoman at 2:13 PM on March 9, 2008

I think it might be a forest plot (a plot of the odds ratio).
posted by Jasper Friendly Bear at 2:14 PM on March 9, 2008

Ok, here is what I am looking for. You would use it if you wanted to (for example) compare world economies. You would set Canada's economy as the baseline and every other economy would be either above or below Canada's by whatever amount.
posted by arcticwoman at 2:24 PM on March 9, 2008

This is a column chart where the category axis crosses at a non-zero value.

1. Highlight your data and make a regular column chart in Excel.

2. The x-axis on the chart Excel displays will go through zero. Right click on the y-axis of the new chart and choose Format axis from the context menu.

3. Then click on the scale tab. There will be an option that says “Category (X) crosses at “ and it will be set to zero. Change this value to 1 (or what ever you value you want to use as the base).
posted by Jasper Friendly Bear at 2:37 PM on March 9, 2008

A simple column/bar chart should do what you need. The key is, you need to normalize the numbers before graphing.

So if Canada's value is 10, the US is 5, and Mexico is 15, you'd use formulas to make cells containing the difference:

US (USCell-CanadaCell)
Mexico (MexicoCell-CanadaCell)

Which would give you cells with values:
US: -5
Mexico: 5

Plotting these with a standard bar chart will allow numbers to go above and below the line. (you may or may not have to adjust the axis settings manually)
posted by chrisamiller at 2:41 PM on March 9, 2008

I've never known this to have a special name. I just call it a bar plot. How else would you deal with negative values in a bar plot?

Of course, your value is not negative, it is less than 1. In Excel 2007, you can change the horizontal axis from 0 to another value by right clicking on the y-axis, Format Axis..., Axis Options, Horizontal axis crosses: Axis value.
posted by grouse at 2:42 PM on March 9, 2008

The key is, you need to normalize the numbers before graphing.

Chrisamiller has it. Just "recenter" your numbers and do a straight bar chart.
posted by Blazecock Pileon at 2:43 PM on March 9, 2008

Beautiful. For some reason, what Jasper Friendly Bear suggested didn't work (although I'm not sure why) but chrisamiller's suggestion did. Thanks all.

Now, is there any way I could make the bars above the line one colour, and those below the line another?
posted by arcticwoman at 2:57 PM on March 9, 2008

Now, is there any way I could make the bars above the line one colour, and those below the line another?

By hand, you can select an individual series element (or shift-click to grab a group) and right-click to select "Options" > "Fill" to change the bar color.

To do this automatically, you'd presumably write a VBA script that assigns a color conditional on the series element value.
posted by Blazecock Pileon at 3:10 PM on March 9, 2008

If you don’t have too many values, you can change the colour of individual bars one by one.

To make your bars different colours, you would

1. Click on one of the bars. This will cause the all of the bars to be selected, Then wait a second and you’ll see a “pop up” showing the value for the individual bar. Once you see this, click on the bar again. This will cause the individual bar to be selected.

2. Right click, and choose Format Data Point and then from the patterns tab choose a new colour for the individual tab.

3. Then choose the next bar whose colour you want to change and press “Ctrl Y” to repeat.
posted by Jasper Friendly Bear at 3:11 PM on March 9, 2008

Now, is there any way I could make the bars above the line one colour, and those below the line another?

Besides manually coloring in each bar, the other way is to plot all the positive values in one series and all the negative values in another series with a common set of x-axis values. Give them the same y-axis parameters and they'll line up and overlap. Then you can select all the positive values series at once and the negative value series separately.
posted by junesix at 7:04 PM on March 9, 2008