Stacked Column Graph Glitch

**KateKoz** · 10-14-2008, 03:32 PM

I'm having a huge problem with a Stacked Column Graph.
The bottom half (red striped) graphs the percentage achieved. The remaining (grey) area graphs the percentage away from 100%.

Basically, this is a thermometer-type graph, showing how close you are to the goal.

The graph works perfectly as long as the percentage is not 83%-99%. At this point, the graph breaks down and becomes innacurate.

I have cut out all "useless" data from these sheets and left only what's needed for this issue. The data is in Sheet 1 and links to the graph in Sheet 2.

The numbers in green (in Sheet 1) are the ones being graphed. The number in blue can be changed to get the desired percentage. If you change the value in blue to anything within the range of $7600 - $9100 you will see the error occur.

ANY help with this would be GREAT!! I have no idea why this is doing this or how to fix it.

**zhollett** · 10-14-2008, 04:23 PM

I have no idea why but if you change the series order (100% on top and Series1 on bottom) the chart works perfectly... upside down mind you.

I dont know why but it might give you something to work with. I know its not a fix, maybe you can figure out whats wrong knowing that bit of info.

Hope you get it.
z

**zhollett** · 10-14-2008, 04:38 PM

Ohh, now I see whats going on here. Its automatically choosing the scale. Its not conforming to the Min = 0 Max = 1 format. When you put the numbers to 86%, it scales the chart so that the min becomes 0.84 and the max becomes 1.0

I got it to work by editing the gridlines and choosing scale. I manually put the min at 0 and the max at 1.

Works great. Attached it the file I used.

**Andy Pope** · 10-15-2008, 04:12 AM

As zhollett points out the problem is the auto scaling.
You just need to uncheck the Min and Max values.

You may find using a clustered column chart is easier.
This will also allow you to use the builtin data labels to display both percentages.

And if you want to get really creative
http://www.andypope.info/charts/thermometer.htm

**KateKoz** · 10-15-2008, 10:16 AM

This works great, thank you! I don't understand how you set the min and max at 0 and 1, though. How do you specify that on the graph?

Those thermometer templates are beautiful! I was actually trying to make a thermometer, and just drew a circle with the draw functions and layed it over my graph. These are much better, though, so I'm going to try to understand them so I can use them.

Thank you guys SOOOO much for your help!!!

**Andy Pope** · 10-15-2008, 10:19 AM

To set the min/max you can either,
double click the Y axis, to display the format dialog. On the Scale tab set Min=0 and Max=1

Or as zhollett described. Select horizontal gridlines and via their format dialog you can also set the Scale properties.