Power functions

Power Function

Y=X^n

n is a positive integer.
The restrictions on the VBA Exponent “^” operator are –
X can be negative only if the exponent n is an integer value.
if X\geq0 then n can be any real.

Function Pow_01(x As Double) As Double
'power function
'Y = X ^ n
Pow_01 = x ^ a
End Function

 

 

Negative Power Function

Negative_power_12

Y=\frac{1}{X^n}
or
Y=X^{-n}

n is an integer to allow negative values of X
if X>0 then n can be any real.

Function Pow_05(x As Double) As Double
'negative power function
'Y = 1/X ^ n
Pow_05 = 1 / (x ^ a)
End Function

 

Fractional Power Function

Fractional_power_1

Y=X^\frac{1}{n}

When n is even, the function is only valid for values of X\geq0.
When n is odd, the function is valid for all values of x.

However, Excel VBA 2013 has an issue with the exponent operator, according to help for “Exponentiation Operator” –
Result = number ^ exponent
“number can be negative only if exponent is an integer value”

Actually this is more restrictive than the legal mathematical values. The third root of -8 is -2. There is no second root of -4.

Y=X^\frac{1}{3}
is valid for all values of X including negative

Y=X^{0.3}
is not valid for negative values of X.

it does not seem possible to coax the vba “^” operator into taking anything other than a double, however the spreadsheet “power” function does accept

Y=X^\frac{1}{3}
for all values of X.

When n is even, there is no nth root defined when x is negative. In that case we check the value of n before the function is called. If it is even, we overwrite the value of Xmin to zero.

If A Mod 2 = 0 Then
xmin = 0
End If

Function Pow_06(x As Double) As Double
'fractional power function
'Y = X ^ 1/n
Pow_06 = Application.WorksheetFunction.Power(x, 1 / a)
'Pow_06 = x ^ (1 / a)
End Function
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s