In Custom Axis, Y = 1, 2, 4, 8, 16 I showed axes with base 2 logarithmic scales in both Excel 2003 and 2007. In Excel 2003 it is necessary to transform the data to get the intended result. In Excel 2007, the axis can be achieved with the untransformed data.
Base Of Natural Log In Excel
How to Calculate Antilog of a Natural Logarithm in Excel A natural logarithm of a number is its logarithmic value to the base-e. It is represented using the symbol ‘ln’. So the natural log of a value, x would be written as ln (x).
The natural logarithm is the logarithm to the base e. The constant e is approximately 2.718282. You can calculate base-n logarithms for any number x by dividing the natural logarithm of x by the natural logarithm of n as follows: Logn(x) = Log(x) / Log(n) The following example illustrates a custom Function that calculates base-10 logarithms. Please see below. Excel has an exponential function and a natural log function. The function is =EXP(value) and it gives the result of e^(value) (this is called syntax). For example, to find the value of e, we can write =EXP(1). Further if we put a number x in A1 and in A2 we put the formula =EXP(A1^2-1), this gives us e^(x^2-1). In other words, whatever is in the exponent goes in the.
In the previous post, the data was widely spaced, so it filled a base 10 log scale with two decades with only a reasonable amount of space above and below the data.
If the data isn’t spread out so nicely, you lose the opportunity to use a regular logarithmic axis. We’ll look at this for Excel 2003 and for Excel 2007, and then we’ll look at native logarithmic axis labeling.
Excel 2003
The data in the table below has a narrow range, from 8 to 12, and the range spans a power of ten.
Here is the data charted using a linear axis.
When we apply a logarithmic scale axis, the data spans across 10, so by default the axis ranges from 1 to 100. The data is squeezed into the middle of the chart.
Since Excel 2003 only permits the axis to begin and end at powers of ten, we’re stuck with this, and the fanciest labeling doesn’t make the data easier to read.
Following the steps in Custom Axis, Y = 1, 2, 4, 8, 16 we can plot the logs of the data on a linear scale, from log(8) = 0.903 to log(12) = 1.079.
We can hide the default labels, add a series with points where we want our custom labels using log(Y) data, and use the Y values as data labels.
Here’s the chart. With such a narrow spread in the data, it’s not immediately apparent that the Y scale isn’t linear, but if you took out your ruler (you still have one of those, right? you’re not completely digital?) you can tell that the span between 8 and 9 is larger than that between 11 and 12.
Excel 2007
As described in Custom Axis, Y = 1, 2, 4, 8, 16, Excel 2007 makes working with log scales a bit easier.
Here is the data plotted on a linear scale.
Natural Log In Excel Formula
Here is the same chart, with the scale transformed to a logarithmic scale, using the default base 10. Same as Excel 2003.
Here the log scale has been changed to base 2.
In 2007 we can change the min and max of the log axis to values other than 10 (i.e., other than the base). Here the axis ranges from 8 to 80, still a decade on the base 10 log scale. Instead of the minor ticks being located at multiples of ten (20, 30, 40, 50,…), they are located at multiples of 8 (16, 24, 32, 40,…)
We can also change the maximum so that the axis spans a non-integral number of cycles. Here the maximum is 12 to fit the data. This looks the same using base 10 or base 2.
We don’t get labels other than at the minimum because the axis spans less than a power of base 10 or of base 2, but we can use the same protocol as above to add points with data labels. The advantage Excel 2007 has over 2003 is that we can use the actual values to locate the points, and we can simply use the Y value data label option. In Excel 2003 we had to use the log of the Y positions, and either manually edit the labels, or use a third-party add-in. (Even though the add-in is great, it still is something extra required.)
Finished. Again, just like Excel 2003, only easier.
Log Axis Labeling
Below are a few variations on a log axis that spans six cycles. These were produced in Excel 2003, but they would be the same in 2007. The scales show both major and minor tick marks, which are available for any base except for 2.
The first scale runs from 1 to 1,000,000, and the second from 1/1,000,000 to 1. The third and fourth run from 1 to 1,000,000 like the first, but instead of the default major unit (major tick spacing) of 10, the third has a major unit of 100 and the fourth of 1000. The minor tick spacing is ignored.
If you use the default major unit, minor ticks are placed at multiples of the number at the start of the cycle. In the Excel 2007 chart above that used a base 10 log scale and ranged from 8 to 80, the minor ticks were at multiples of 8 (16, 24, 32, 40,…). In our first axis below, the bottom of the decade is at 1, so minor ticks are at multiples of 1 (2, 3, 4, 5,…). The the second axis, the bottom of the decade is at 0.1, so minor ticks are at 0.2, 0.3, 0.4, and so forth.
In 2003, when the non-default major unit is used, the space between major tick marks is divided up as if it were a single cycle (not two or three. Instead of the ticks being located at 2, 3, 4, etc, for a major unit of two cycles, the minor ticks are located at the square of the one-cycle values, that is, at 4, 9, 16, etc. For a major unit of three cycles, the minor ticks are located at the cube of the one-cycle values, that is, at 8, 27, 64, etc. Interesting.
Excel 2007 does this a bit differently, and for a two-cycle major unit, it makes sense. The first pair of axes below show a scale of 1 to 100, first with the major unit defined by the default one cycle, then by two cycles. I’ve added labels to many of the minor ticks to help illustrate this behavior. The two cycle axis has the first minor tick where the first cycle ends and the next cycle starts, then the rest of the ticks are where they were defined for the second cycle. This puts the ticks at the very logical values of 10, 20, 30, etc.
With larger spans between major ticks, Excel 2007 seems to get confused. The second pair of axes shows three cycles with major units of one and three cycles. The three-cycle axis places minor ticks where the two-axis tick did in the first instance, at multiples of 10 between 10 and 90. There is no tick at 100, nor any between 100 and 1000. Not shown is a four-cycle axis. This puts minor ticks at multiples of 10 between 10 and 90, and at multiples of 1000 between 1000 and 9000, but skips the decade between 100 and 1000.
– – –
This is pretty obscure. It doesn’t make sense to use multiple cycles as the major unit. In a two-cycle major unit, Excel 2007 uses more logical minor tick spacing, while the minor tick spacing is broken for larger major units. Excel 2003 uses a consistent minor tick spacing which is logical in its own way, but not readily understandable.
Common (base ten) logarithms
The best way to understand logarithms is through an example. If you take 10 to the third power (10 x 10 x 10) the result is 1000. The logarithm is the inverse of that power function. The logarithm (base 10) of 1000 is the power of 10 that gives the answer 1000. So the logarithm of 1000 is 3. If you multiply 10 by itself 3 times, you get 1000.
You can take 10 to a negative power. For example, taking 10 to the -3 power is the same as taking the reciprocal of 103. So 10-3 equals 1/103, or 0.001. The logarithm of 0.001 is the power of 10 that equals 0.001, which is -3.
You can take 10 to a fractional power. Ten to the 1/2 power equals the square root of 10, which is 3.163. So the logarithm of 3.163 is 0.5.
Ten to the zero power equals 1, so the logarithm of 1.0 is 0.0.
You can take the logarithm of any positive number. The logarithm of values between zero and one are negative; the logarithms of values greater than one are positive. The logarithms of zero and all negative numbers are undefined; there is no power of 10 that gives a negative number or zero.
Logarithms to other bases
The logarithms shown in the previous section are called base 10 logarithms, because the computations take 10 to some power. These are also called common logarithms.
You can compute logarithms for any power. Mathematicians prefer natural logarithms, using base e (2.7183…). Despite that name, use of logarithms with base e does not seem natural to most scientists who tend to be far more comfortable with base 10 logarithms.
Biologists sometimes use base 2 logarithms, often without realizing it. The base 2 logarithm is the number of doublings it takes to reach a value. So the log base 2 of 16 is 4 because if you start with 1 and double it four times (2, 4, 8, and 16) the result is 16. Immunologists often serially dilute antibodies by factors of 2, so often graph data on a log2 scale. Cell biologists use base 2 logarithms to convert cell counts to number of doublings.
Logarithms using different bases are proportional to each other. So converting from natural logs to common logs is sort of like changing units. Divide a natural logarithm by 2.303 to compute the common log of the same value. Multiply a common log by 2.303 to obtain the corresponding natural log.
Mathematical properties of logarithms
Logarithms convert multiplication into addition and division into subtraction, and exponentiation into mulitplication:
log(A.B) = log(A) + log(B)
log(A/B) = log(A) - log(B)
log(An) = n.log(A)
Antilogarithms
The antilogarithm (also called an antilog) is the inverse of the logarithm transform. Since the logarithm (base 10) of 1000 equals 3, the antilogarithm of 3 is 1000. To compute the antilogarithm of a base 10 logarithm, take ten to that power.
To compute the antilogarithm of a natural logarithm, take e to that power. The natural logarithm of 1000 is 6.980. So the antilogarithm of 6.908 is e6.908, which is 1000. Spreadsheets and computer languages use the notation exp(6.908).
Terms that sound similar to logarithm: logit and logistic
The term logistic sounds like it is related to logarithms. In fact, the term logistic has three distinct meanings, which don't all involve logarithms.
Using Natural Log In Excel
The logit function, used in logistic regression, is the natural logarithm of a probability (P, with a value between 0.0 and 1.0) divided by 1-P:
Logit(P) = ln[P/(1-P)]
The lognormal distribution is a distribution of values whose logarithms follow a Gaussian distribution. The lognormal distribution itself is asymmetrical with a heavy tail to the right. It is easy to be mislead by the asymmetry, and mistakenly eliminate values in the tail of the distribution as outliers.
Natural Log
The notation “log(x)” can be ambiguous
Within Prism transforms and equations, the log() function computes the common (base 10) logarithm.
With some computer languages (for example, Javascript) log(x) denotes a natural logarithm, not a common logarithm. Excel is especially confusing. In an Excel worksheet function, log(X) is the common logarithm, but in an Excel VBA macro, log(X) is the natural log.
The notation “ln(X)” is the natural logarithm in Prism.