Trouble with Chart that uses Log Scale

[Alfonso Navarro]

Hey Guys I can't seem to figure this out. I have a chart that uses extremely wide ranges of data. Low point being 5,000 high point being $15B. I plotted the data on a line chart using a log scale. The problem is that the data appears as almost straight lines despite the wide ranges in value.

Please help!

 

[NARAYANK991]

Hi Alfonso ,

Things are not so simple !

If you see your values , they range from a few thousands to many billions ; plotting these is not a matter of just changing the scale to logarithmic , since what you are looking for is not to merely plot the various values but show the variations ( changes ) that happen within these values ; the logarithmic scale will not show these changes since they are large in relation to the value , but not large in relation to the scale.

Thus , if a value of 6000 changes to 7000 , the change is more than 15 % in relation to 6000 , but in terms of the logarithmic scale ranging from 1000 to 10 billion , the change is insignificant !

First , do not use the logarithmic scale.

Second , do not plot the values as they are ; try to introduce helper rows , changing the units of certain variables ; dividing a billion by 1000 will reduce it to a million.

Third , segregate the values so that one set can be plotted on the primary axis , while another set can be plotted on the secondary axis.

Narayan

 

[KenU]

@Alfonso Navarro,

I believe that you may understand the plot better by calculating the log of the values in each series (i.e., =LOG(B2), etc.). You will then see that the plot is correctly representing the values on a log scale. Taking the values in row 2, they range from 6.38 billion to 13.98 billion. However, the logs of these values are 9.8 and 10.1, respectively. Therefore all of row 2 appears to plot as a somewhat horizontal line.

I hope that helps.

Regards,
Ken

 

[Mike86]

The easiest way to put all the lines on the same chart would be to normalize the data. In your case, I'd suggest taking the max value from each row, dividing each cell in the row by the max number, and multiplying each cell by 100. The Y-axis can now be 40 to 100 and the data shows percentage changes. You also don't need one of the bottom two rows as the normalized data is the same for both. Also, use the year as the x-axis values.