Excel Help, Please!

natural log #s

2.2 0.78845736

3.4 1.223775432

1.7 0.530628251

9.6 2.261763098

if you are only interested in DISPLAYING the REALTIVE DIFFERENCES between each data point (i.e. relative change), then a log chart should do it. The data can be preneted in a table or attached as a label. So make the orginal number the row name in "series", then display data labels clicking "category name". then remove values for x and y axes.

You have log values for bars, but orignal values as labels.

try this...

How about just changing the value of the outlier in the data table and place the correct value on the label. That way you can apply whatever scale you want but still show the correct values..

I recommend simply changing your data to a number that suits you. Good scientists do this all the time. I'd pick 2.1 for D. Good luck.

I doubt that Excel can do what the original poster wanted. I do know that S-Plus and SAS can do it. Other stat or graphing programs can probably do it too.

.. wrote:

Graph both the 'a' and 'b' data series. The 0s will not show on the graph. Right-click on the amt3 series and select Format Data Series. The second tab will have an option to plot series on Secondary axis. You can then change the second axis to suit. Just make sure you label both Y-axis so you are clear as to what data set refers to what axis.

I take this guy's advice and put it on a second y-axis, although it probably still won't display it the way you'd like.

The only way that I know to make a split bar would be to draw it yourself on the chart, but you'd also have to break the y-axis also. I'd make a chart in Excel with 2 bars, then import the chart into PowerPoint. Within PowerPoint you can use the draw tool to draw the bar you want and modify the y-axis to show the correct numbers. To save the image you might have to use cntrl-printscreen and modify it in your paint program, but there could be an easier way. Try some google searches.

Others have provided some of the ideas that I had when I saw your post (e.g., second axis or using the logs; using the logs makes more sense if the nature of the data is 'multiplicative). (Note that Malmo does a lot of data manipulation/presentation; he has a lot of experience with using data in different ways. I have mostly seen his presentations here using running data, but I am willing to bet his expertise extends well beyond 'avocation').

Another possibility, but one dependent on how you think of the data is to do the following. In graph I plot A, B and C. In graph two plot D and the average of the other three, A, B, and C. The first graph shows the important differences between A, B and C. The second shows how different D is from the other three. This is not a great solution but might also be doable.

Off the Grid wrote:

FIght Time wrote:

The data is not an oulier, it is just a much higher value than the others.

...definition of an outlier.

Another possibility is to present the data as a bubble chart. this may compensate.

But it sounds like you should take the log of each number =LN(Cell) or =LOG(Cell, base) and chart it.

That is not the definition of an outlier. Take a statistics class.

Thanks, but as I mentioned, this type of data is not traditionally shown in log form. I have other graphs in the same paper that are not in log form as well, so it would look rather strange. I think I will just do some cutting and pasting in Powerpoint or Photoshop using two different graphs.

do a 2nd set of data, then draw a f***ing squiggly line to show that it's an interrupted axis.

If you are intent on keeping decimal scale, then another alternative is to create a 3-period vertical gantt chart. make all the 2nd period sections the same as the chart background color w/ no borders, and do the same for the 3rd periods for A,B & C. for D, leave the original color.

Make the 2nd period size large enough so that the top of D is in the chart and does not distort the relative scale of A B & C. I believe you can alter the border of D to indicate it is truncated.

A couple months back I did a graph in Excel and had the same problem, except I had a huge section on the x-axis I wanted to collapse. I finally had to stretch the x-axis very long, then save the graph into a graphics program and remove the center portion...not the most elegant solution. I've used other programs that can do this, as others have suggested, but usually prefer to work in Excel for 2-D graphs. That's why I ended up doing it that way. If you have another graphing package you're familiar with and can work in it, better to use that for this problem.