Exploring Exponential and Logarithmic Functions

2017-11-03 09:05:03

Problem

You need to compute logarithms in your calculations but aren't familiar with Excel's support for such calculations.

Solution

Excel provides nine built-in functions for computing logarithms and one exponential function (excluding the special class of exponential functions known as hyperbolic functions, for which Excel provides six built-in functions). A few of these logarithm functions are related to the log-normal probability distribution, while others are related to complex numbers. See Chapter 6 for recipes dealing with probability and check out Recipe 7.13 for dealing with complex numbers. Read the following discussion to learn about the remaining logarithmic functions .

Discussion

Table 7-4 shows several of Excel's built-in logarithmic and exponential functions.

Table 7-4. Common logarithmic and exponential functions
Function	Syntax	Description
`LOG`	`=LOG(n,` `base)`	Returns the log of the number n to the specified base
`LOG10`	`=LOG10(n)`	Returns the log of the number n to the base 10
`LN`	`=LN(n)`	Returns the natural logarithm of n
`EXP`	`=EXP(n)`	Returns en
`POWER`	`=POWER(n,` `p)`	Returns np and is the same as the syntax n`^`p using the `^` operator
`SINH`	`=SINH(n)`	Returns (en - e-n)/2
`COSH`	`=COSH(n)`	Returns (en + e-n)/2
`TANH`	`=TANH(n)`	Returns (en - e-n) / (en + e-n)
`ASINH`	`=ASINH(n)`	Returns the inverse hyperbolic sine, ln(n + sqrt(n2+1)), where n is any real number
`ACOSH`	`=ACOSH(n)`	Returns the inverse hyperbolic cosine, ln(n + sqrt(n2-1)); n must be greater than 1
`ATANH`	`=ATANH(n)`	Returns the inverse hyperbolic tangent, ln((1+n)/(1-n))/2; n must be between -1 and 1

These functions pretty much do what you'd expect functions related to logarithms and natural logarithms to do. You may wonder why there's a LOG10 function when the LOG function with a specified base of 10 performs the same computation. Moreover, if you leave the base out of a call to LOG, then base 10 is assumed. It seems redundant, but perhaps some people prefer typing LOG10(n) to typing LOG(n,10) or even LOG(n).

Excel also provides the exponential function EXP(n) to compute en, which is of course the inverse of the natural logarithm function.

Aside from straightforward logarithmic and exponential computations, these functions are useful when curve fitting. For example, you may have data through which you'd like to fit a logarithmic curve. You could use Excel's built-in logarithmic curve-fitting features (see Chapter 8) or you could transform the data and use Excel's linear curve-fitting functions. For example, LINEST(y values, LOG(x values)) uses Excel's linear curve-fitting function on the transformed data, which results in a logarithmic curve fit. Moreover, sometimes it's more convenient or insightful to plot the log of a dataset instead of the dataset in its original form. You can use these functions to transform your data as desired.

Excel also has a function called POWER that you may find useful. The syntax for POWER is =POWER(n, power), where n is a given number and power is the power to which n will be raised. You can also use the ^ operator to raise a number to a power, as discussed in Recipe 1.9. Conversely, you can use POWER to reverse a LOG operation on a value. For example =POWER(10, LOG(3)) simply returns a value of 3.

This all seems straightforward enough, and it is, but there's potential for confusion when working with Excel's built-in logarithmic functions and the Log function available in VBA (Visual Basic for Applications; see Chapter 2 for a quick introduction). VBA provides the function Log(n), which returns the natural logarithm of the given number n, not the base 10 logarithm as you might expect. This clearly is not consistent with Excel's built-in logarithmic functions, where LOG means base 10 log and LN means natural log.

VBA does not provide another function for computing the base 10 logarithm of a number (or any other base, for that matter). However, when working in VBA you can compute the logarithm of a number to any base by taking the ratio of the natural logarithm of the number to the natural logarithm of the desired base. For example, to compute the base 10 logarithm of n, you'd write Logn = Log(n) / Log(10.0), where Logn is just a variable you'd define to store the result (you can name it whatever you want, of course).

Категории