using a function

[Jeff]

This problem is driving me crazy - please help.

The problem in a nutshell is as follows:

Sub Macro2()
Dim xxx As Double
xxx = 0.05
Call NewFuntio(xxx)
End Sub

Sub NewFuntio(temp)
Dim t1,t2 As Double
t1 = (temp + 1) ^ (1 / 12)
t2 = 1.05^(1/12)
End Sub

t1 = 1.00407412390531
t2 = 1.00407412378365

t1 is the wrong answer and t2 is the correct answer.

Why is the function giving the wrong answer?

Thanks

[Bernie Deitrick]

Jeff,

t1 and t2 are identical for me - perhaps try dimensioning better....

Sub Macro2()
Dim xxx As Double
xxx = 0.05
Call NewFuntio(xxx)
End Sub

Sub NewFuntio(temp)
Dim t1 As Double
Dim t2 As Double
t1 = (temp + 1) ^ (1 / 12)
t2 = 1.05 ^ (1 / 12)
MsgBox t1 & Chr(10) & t2

End Sub

HTH,
Bernie
MS Excel MVP


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> This problem is driving me crazy - please help.
>
> The problem in a nutshell is as follows:
>
> Sub Macro2()
> Dim xxx As Double
> xxx = 0.05
> Call NewFuntio(xxx)
> End Sub
>
> Sub NewFuntio(temp)
> Dim t1,t2 As Double
> t1 = (temp + 1) ^ (1 / 12)
> t2 = 1.05^(1/12)
> End Sub
>
> t1 = 1.00407412390531
> t2 = 1.00407412378365
>
> t1 is the wrong answer and t2 is the correct answer.
>
> Why is the function giving the wrong answer?
>
> Thanks
>

[Bob Phillips]

When you declare two variables like that

Dim t1, t2 as Double

you are not declaring two doubles as you think, but one variant, one double.

Use

Sub Macro2()
Dim xxx As Double
xxx = 0.05
Call NewFuntio(xxx)
End Sub

Sub NewFuntio(temp)
Dim t1 As Double, t2 As Double
t1 = (temp + 1) ^ (1 / 12)
t2 = 1.05 ^ (1 / 12)
End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jeff" <[email protected]> wrote in message
news:[email protected]...
> This problem is driving me crazy - please help.
>
> The problem in a nutshell is as follows:
>
> Sub Macro2()
> Dim xxx As Double
> xxx = 0.05
> Call NewFuntio(xxx)
> End Sub
>
> Sub NewFuntio(temp)
> Dim t1,t2 As Double
> t1 = (temp + 1) ^ (1 / 12)
> t2 = 1.05^(1/12)
> End Sub
>
> t1 = 1.00407412390531
> t2 = 1.00407412378365
>
> t1 is the wrong answer and t2 is the correct answer.
>
> Why is the function giving the wrong answer?
>
> Thanks
>

[[email protected]]

"Jeff" wrote:
> t1 = 1.00407412390531
> t2 = 1.00407412378365
> t1 is the wrong answer and t2 is the correct answer.
> Why is the function giving the wrong answer?

First, you should identify the version of Excel that you are using. I see
no difference with my version of Excel, Office Excel 2003, at least when I
use msgbox to look at t1 and t2.

> Sub NewFuntio(temp)
> Dim t1,t2 As Double
> t1 = (temp + 1) ^ (1 / 12)
> t2 = 1.05^(1/12)

Try changing the "dim" declaration to:

dim t1 as double, t2 as double

which I suspect is your intent.

As you have written, t1 is declared a variant, not a double. I suspect that
in your version of Excel, variants that contain floating point numbers are
treated as "single", double. I was unable to duplicate your results exactly
by forcing t1 to be type single, but that might be because I am not being
careful with the type of intermediate computations. Nonetheless, I do get a
difference when I store t1 into a double (t3).