what's wrong with custom function?

[Juggernnath]

Function RsGlaso(gama, API, T, Pb)
' Application.Volatile False

Dim Rs As Double, KorekcioniPb As Double
'gama = gas gravity - input testData=0.732
'API = ro [API] - input testData=38
'T - [F] - input testData=180
'Pb - [psi] - input testData=3811

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
'Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
RsGlaso = 5
End Function


It works if I type formulas into spreadsheet, but if I defined it as custom
function it returns #VALUE

 

[Dana DeLouis]

It appears to me that you will get a value error when taking the square root
of a negative number.

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)

Perhaps re-written as:
KorekcioniPb =
10^((28869 - 100*Sqr(141811 - 33093*Log(Pb))) / 10000)

I think you should get a Value error when Pb is at your test value of 3811
because this causes a negative square root.
Looks to me that Pb should be in the range of 0 - 72.619...

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Function RsGlaso(gama, API, T, Pb)
' Application.Volatile False

Dim Rs As Double, KorekcioniPb As Double
'gama = gas gravity - input testData=0.732
'API = ro [API] - input testData=38
'T - [F] - input testData=180
'Pb - [psi] - input testData=3811

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
'Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
RsGlaso = 5
End Function


It works if I type formulas into spreadsheet, but if I defined it as custom
function it returns #VALUE

 

[J.E. McGimpsey]

I don't get the #VALUE error unless

API < 0: API ^ 0.989 fails,
T = 0: division by 0 in the Rs= line,
Pb <= 0: Log(Pb) fails,
or
Pb > about 72.5: (14.1811 - 3.3093 * Log(Pb)) ^ 0.5 fails.

On the other hand, since you put RsGlaso = 5, the function will, if
it doesn't error, always return 5. That can't be right...

 

[Juggernnath]

LOG(3811)=3.5810389
*3.3093=11.850732
14.811-11.850732=2.9602678
SQR(2.9602678 >0) = 1.72
I didn't understand if you made calculating mistake or tried to say
something else...
================================================================

It appears to me that you will get a value error when taking the square root
of a negative number.

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)

Perhaps re-written as:
KorekcioniPb =
10^((28869 - 100*Sqr(141811 - 33093*Log(Pb))) / 10000)

I think you should get a Value error when Pb is at your test value of 3811
because this causes a negative square root.
Looks to me that Pb should be in the range of 0 - 72.619...

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Function RsGlaso(gama, API, T, Pb)
' Application.Volatile False

Dim Rs As Double, KorekcioniPb As Double
'gama = gas gravity - input testData=0.732
'API = ro [API] - input testData=38
'T - [F] - input testData=180
'Pb - [psi] - input testData=3811

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
'Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
RsGlaso = 5
End Function


It works if I type formulas into spreadsheet, but if I defined it as custom
function it returns #VALUE

 

[J.E. McGimpsey]

In VBA, LOG() is the natural log function. In XL, LOG(x) is the log
to base x, with the default being base 10.

If you want log base 10 in VBA, use Log(x)/Log(10)

?Log(3811)/Log(10)
3.58103894877217

 

[Dana DeLouis]

Hi. Looks like J.E. caught the problem here. :>) Here are some
additional ideas for consideration:

Sub Demo()
'VBA
Debug.Print Log(3811)

Worksheet Function
Debug.Print WorksheetFunction.Log(3811, 10)
Debug.Print WorksheetFunction.Log10(3811)
End Sub

returns...
8.24564690087386
3.58103894877217
3.58103894877217

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

LOG(3811)=3.5810389
*3.3093=11.850732
14.811-11.850732=2.9602678
SQR(2.9602678 >0) = 1.72

<snip>