EXCEL: Logarithmic Scale - Negative Values 1

[jmgibson]

Hi,

I'm trying to graphical represent two data series/sets on a graph in Excel (with a primary and secondary y axis). The Data covers a LARGE range of values, so a Logarithmic scale for both the primary and secondary axis works great. However, I have a few negative values (which as we know, don't work with logarithmic scales)that need to be factored into the graph. Without a logarithmic scale, much of the value is taken out of the graph since I have some data points at $1,000 and others at $100,000,000,000. Without the logarithmic scale, folks really only see the large values.

Any clever thoughts on how to take advantage of the logarithmic scale and still factor in my negative values?

 

[SkipVought]

Plot the negative values as positive, but in a different series.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[jmgibson]

I'm not sure I follow.....how would this appear as negative on the charts? I'm looking at account balances and I need to be able to reflect that an account is in negative territory? Perhaps I'm overlooking something simple. Thanks Skip!

 

[SkipVought]

As you stated, a log scale cannot display negative values. So in order to DISPLAY the value, you must change the sign and show it in a second series, the data points of which, for instance, you could shade with RED, to indicate negative values.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[SkipVought]

For instance, I created some test data...
[tt]
values
10000000
100000
20000
5000
700
50
-100
-7000
-50000
[/tt]
then a second column for the negative values...
[tt]
values NEG
10000000 0
100000 0
20000 0
5000 0
700 0
50 0
-100 100
-7000 7000
-50000 50000
[/tt]
the values plot like this with TWO series...
[tt]
*


* [red]-[/red]
* [red]-[/red]
*
*
[red]-[/red]
*
[/tt]




Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]