Log-scale histograms

Originally Posted by shg

but for the x axis, it MUST BE a scatter chart. Change the chart type.

Thanks again. As the title of the thread suggests, I'm trying to draw a histogram, not a scatterplot. (Since I'm trying to visualise the distribution of a single variable, not compare two variables, a scatterplot isn't appropriate.)

As Microsoft, in their wisdom, have deemed log transformations of the x axis unneccessary, I suppose a hack is in order. So, it possible to either

1. Hack one of the other chart types to display histograms bars? The scatterplot chart definitely do log-x-scales but can it display bars? The barplot chart type can obviously display bars, but will it allow for a continuous variable on the x-axis?

2. Plot a histogram of the log-transformed variable and override the axis labels with natural scale values, as I first suggested?

I appreciate this is a very old thread, but for the benefit of others looking for a solution to this:

You can use the FREQUENCY function to bin your data into whatever chunks you need to, with the syntax being =FREQUENCY(data_array, bins_array). Your bins array will contain the lower bounds of the bin, for example if the first couple of cells are 20,21,22... then the first row of your results will show the frequency of a value between 20-21, the next 21-22 etc.

Information about the usage of this function can be found online, but in short if you have your data to be binned in cells A1:A100 then you just need to create your bins array (e.g. in cells B1:B30), then highlight cells C1:C30 and type in the FREQUECNY function then press CTRL+Shift+Enter to complete the operation.

I then use a helper column to contain the upper bound value of the bin (simply by referencing the next value in the bin array), then use a concatenate function to create a reasonable value for the x axis of your plot - e.g. if your lower bound value is in cell B1:B30 and the upper bound is in D1:D30, then in cells E1:E30 you will write something like =CONCATENATE(B1," - ",D1).

From here, a simple bar chart can be used which will allow a log scale be selected as described in this thread.

Hope this helps someone.

I'm confused by this: how does creating the helper column and using the concatenate function work to allow a log scale to be used in the bar chart?