Plotting Zero Values (Logarithmic Scatter Plot)

Make the zero values #N/A, which will keep them from plotting. Nothing is totally impermeable, it's just to low to measure, and as you point out, 0 has no logarithm.

Originally Posted by shg

Make the zero values #N/A, which will keep them from plotting. Nothing is totally impermeable, it's just to low to measure, and as you point out, 0 has no logarithm.

No offense, but did you read my question? I need to plot the zeros, not "keep them from plotting."

No offense, but did you read my answer? You can't plot zero values on a log axis.

I'm not sure it is a question of whether Excel can do it or not -- it's more a question of what you really want Excel to do. At this point, it seems that you really don't have a good idea of what you want Excel to do. Some thoughts:

1) 0 (zero) has no meaning on a logarithmic axis. One could put an arbitrarily small non-zero value in, but this might unduly obscure the logarithmic axis, depending on what the magnitude of the other values is. Perhaps assign a value of one or two orders of magnitude smaller than the smallest non-zero value to these zero values and plot them. Your readers will need to be smart enough to recognize that this is an arbitrary value that represents 0.

2) Since Excel can only do linear or logarithmic axes, those are the two choices. If you decide the previous suggestion won't work, then I would suggest coming up with a different "transformation" for the axis. Perhaps a square root axis or some other transformation will work. These other axes can be plotted in Excel using variations on this "arbitrary axis" technique, such as this one used for a reciprocal (1/x) axis http://peltiertech.com/Excel/Charts/RecipAxisChart.html At that point, the hardest part will be outside of Excel, trying to decide what kind of transformation will be most useful for your data.

I don't doubt that such "cheats" can be implemented in Excel, they just won't be pre-programmed into Excel for us.

Another thought: use a panel chart http://peltiertech.com/WordPress/bro...n-excel-chart/ One panel could show the plot you have now, not displaying the zero values. The second panel could show the same data on a linear plot to show the relationship of the other data to the 0 values.

Out of curiosity, what kind of "permeability" is this (geologic, chemical, or other? your reference to depth might suggest geologic/earth science type data)? I have to believe that you are not the first one to encounter this issue. How have others in your field presented this kind of data?

Ideally, I'd be able label my lowest x-axis tick mark as <0.1 mD, but to my knowledge that is not possible in Excel.

True, if you limit yourself strictly to built in functionality. I expect this should be fairly easy using the arbitrary axis technique I mentioned earlier (different example here http://peltiertech.com/Excel/Charts/ArbitraryAxis.html).

1) It sounds like the minimum real value is 0.1, so assign those which are sited as 0 the plotted value 0.01.
2) Create a dummy series to be used for the "arbitrary axis". This will need y values at each decade from 0.01 to the desired max value for the y axis. The x value should be chosen to place the axis where you want it (far left, I assume).
3) For the axis labels, you can use the y values as plotted, except for the 0.01 mark, which will be labeled "<0.1".