Stacked columns and Log scales

**nupurgupta123** · 03-16-2010, 06:55 PM

HI, I have a knotty problem. I am including my Excel file.
I have a stacked column chart. I have a logarithmic y-scale. The problem is that if there are empty values in any of the entries, Excel tries to assign it a value.

I tried to avoid this problem by taking log values before I charted them. In this case, my y-axis shows log values, but I need it to show the original values.

Any way around either of these?
Any pointers would be very much appreciated.

**shg** · 03-16-2010, 07:08 PM

I don't understand what you're trying to to do/show/plot ....

**Andy Pope** · 03-17-2010, 09:42 AM

It has to assign a value as zero is not allowed on a log scale.

You can use your formulated version of the data.
Use a cluster column with the overlap set to 100. You also need to reverse the plot order.

To create the correct Y scale use a dummy XY series.

**nupurgupta123** · 03-17-2010, 11:07 AM

Thanks so much for your responses.

Shg - I am trying to create a stacked column chart with a logarithmic y-axis. I have some data elements which don't have a value, so this creates problems because logs always need non-zero data elements.

Andy- Using a 3-D cluster column does not help. I do need a stacked column. What do you mean by a dummy XY series? Should I add another series and set its chart type to XY scatter? How does that alter the labels on my y-axis axis?

Really appreciate your help.

**Andy Pope** · 03-17-2010, 11:20 AM

Who mentioned a 3d cluster column?

Have you looked at the right hand chart in the attachment I posted? Is it the type of chart you want.

This is a clustered 2d column chart, based on your LOG formula. The problem with that is the Y scaling would be 1,2,3,4 etc as the log values return these values. So to mimic the log scaling I used a dummy series plotted as a xy-scatter. This has data labels displayed to the left of the data points to act as the scales value labels.

**nupurgupta123** · 03-17-2010, 12:09 PM

Ohhh... I understand, I am sorry, I did not look at your example excel file closely enough.

Great to use the 'Scale' series. So this has x-values of 0.5... and y-values of 1,2,3. Really sorry, but how did you set the data labels to 10,100,1000. Looking everywhere, can't figure it out.

**Andy Pope** · 03-17-2010, 12:14 PM

The data labels are linked to the cells.

Select the data labels then select an individual data label to see the cell reference in the formula bar.

You can use a free addin to make the task of link easier.
http://www.appspro.com/Utilities/ChartLabeler.htm