Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Slight Difference in Calculation Between Spreadsheet and VBA

I am writing some functions in vba after performing the calculations on a
spreadsheet. When I compare the results, it consistently appears that the
output from the vba function is not as accurate. It seems to lose accuracy
once you get to the 7th or 8th significant digit. Does anyone know why this
happens? Thanks!!
  #2   Report Post  
Posted to microsoft.public.excel.misc
bj bj is offline
external usenet poster
 
Posts: 1,397
Default Slight Difference in Calculation Between Spreadsheet and VBA

Please give an example.

"Clint Schwartz" wrote:

I am writing some functions in vba after performing the calculations on a
spreadsheet. When I compare the results, it consistently appears that the
output from the vba function is not as accurate. It seems to lose accuracy
once you get to the 7th or 8th significant digit. Does anyone know why this
happens? Thanks!!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 837
Default Slight Difference in Calculation Between Spreadsheet and VBA

The most likely explanation for discrpancies in that range is that you have
declared some variables "As Single". If the problem persists after changing
"Single" to "Double", then post your code.

Jerry

"Clint Schwartz" wrote:

I am writing some functions in vba after performing the calculations on a
spreadsheet. When I compare the results, it consistently appears that the
output from the vba function is not as accurate. It seems to lose accuracy
once you get to the 7th or 8th significant digit. Does anyone know why this
happens? Thanks!!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Slight Difference in Calculation Between Spreadsheet and VBA

Here is the code:

P=8e7
T=300

Public Function SpecVolLiq(ByVal P As Double, ByVal T As Double) As Double

Dim Pstar As Double
Dim Tstar As Double
Dim Pi As Double
Dim Tau As Double
Dim n(1 To 34) As Double
Dim I(1 To 34) As Integer
Dim J(1 To 34) As Integer
Dim Sum(1 To 34) As Double
Dim x As Integer
Dim temp As Double
Dim R As Single 'Specific Gas Constant of Water (J/kg K)

I(1) = 0
I(2) = 0
I(3) = 0
I(4) = 0
I(5) = 0
I(6) = 0
I(7) = 0
I(8) = 0
I(9) = 1
I(10) = 1
I(11) = 1
I(12) = 1
I(13) = 1
I(14) = 1
I(15) = 2
I(16) = 2
I(17) = 2
I(18) = 2
I(19) = 2
I(20) = 3
I(21) = 3
I(22) = 3
I(23) = 4
I(24) = 4
I(25) = 4
I(26) = 5
I(27) = 8
I(28) = 8
I(29) = 21
I(30) = 23
I(31) = 29
I(32) = 30
I(33) = 31
I(34) = 32

J(1) = -2
J(2) = -1
J(3) = 0
J(4) = 1
J(5) = 2
J(6) = 3
J(7) = 4
J(8) = 5
J(9) = -9
J(10) = -7
J(11) = -1
J(12) = 0
J(13) = 1
J(14) = 3
J(15) = -3
J(16) = 0
J(17) = 1
J(18) = 3
J(19) = 17
J(20) = -4
J(21) = 0
J(22) = 6
J(23) = -5
J(24) = -2
J(25) = 10
J(26) = -8
J(27) = -11
J(28) = -6
J(29) = -29
J(30) = -31
J(31) = -38
J(32) = -39
J(33) = -40
J(34) = -41

n(1) = 0.14632971213167
n(2) = -0.84548187169114
n(3) = -3.756360367204
n(4) = 3.3855169168385
n(5) = -0.95791963387872
n(6) = 0.15772038513228
n(7) = -0.016616417199501
n(8) = 8.1214629983568E-04
n(9) = 2.8319080123804E-04
n(10) = -6.0706301565874E-04
n(11) = -0.018990068218419
n(12) = -0.032529748770505
n(13) = -0.021841717175414
n(14) = -5.283835796993E-05
n(15) = -4.7184321073267E-04
n(16) = -3.0001780793026E-04
n(17) = 4.7661393906987E-05
n(18) = -4.4141845330846E-06
n(19) = -7.2694996297594E-16
n(20) = -3.1679644845054E-05
n(21) = -2.8270797985312E-06
n(22) = -8.5205128120103E-10
n(23) = -2.2425281908E-06
n(24) = -6.5171222895601E-07
n(25) = -1.4341729937925E-13
n(26) = -4.0516996860117E-07
n(27) = -1.2734301741641E-09
n(28) = -1.7424871230634E-10
n(29) = -6.8762131295531E-19
n(30) = 1.4478307828521E-20
n(31) = 2.6335781662795E-23
n(32) = -1.1947622640071E-23
n(33) = 1.8228094581404E-24
n(34) = -9.3537087292458E-26

R = 461.526
Tstar = 1386
Pstar = 16530000#
Pi = P / Pstar
Tau = Tstar / T

temp = 0
For x = 1 To 34
Sum(x) = -n(x) * I(x) * (7.1 - Pi) ^ (I(x) - 1) * (Tau - 1.222) ^ J(x)
'Debug.Print "Sum( " & x & ") = " & Sum(x)
temp = temp + Sum(x)
Next x
'Debug.Print "Total = " & temp
'Debug.Print Pi
'Debug.Print Tau
SpecVolLiq = Pi * temp * (R * T / P)
End Function

Ouput from Spreadsheet = 0.00097118089402163
Output from VBA Function = 0.000971180896076593

Let me know if you have any ideas. Thanks!!

"Jerry W. Lewis" wrote:

The most likely explanation for discrpancies in that range is that you have
declared some variables "As Single". If the problem persists after changing
"Single" to "Double", then post your code.

Jerry

"Clint Schwartz" wrote:

I am writing some functions in vba after performing the calculations on a
spreadsheet. When I compare the results, it consistently appears that the
output from the vba function is not as accurate. It seems to lose accuracy
once you get to the 7th or 8th significant digit. Does anyone know why this
happens? Thanks!!

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 837
Default Slight Difference in Calculation Between Spreadsheet and VBA

As I originally suggested, change
Dim R As Single 'Specific Gas Constant of Water (J/kg K)
to
Dim R As Double 'Specific Gas Constant of Water (J/kg K)

The single precision approximation to 461.526 is 461.526000976563, which
accounts for your discrepant results.

I assume that your concern was with reliability of calculations in Excel,
vs. VBA. AFAIK, both correctly correctly handle basic arithmetic to the
limit of their accuracy (IEEE double precision, which gives roughly 15 figure
calculational accuracy).

If instead of simple reliability concerns, you really do need results that
are accurate to more than 7-8 figures, then you need find the value of R to
more than 6 figures.

Jerry

"Clint Schwartz" wrote:

Here is the code:

P=8e7
T=300

Public Function SpecVolLiq(ByVal P As Double, ByVal T As Double) As Double

Dim Pstar As Double
Dim Tstar As Double
Dim Pi As Double
Dim Tau As Double
Dim n(1 To 34) As Double
Dim I(1 To 34) As Integer
Dim J(1 To 34) As Integer
Dim Sum(1 To 34) As Double
Dim x As Integer
Dim temp As Double
Dim R As Single 'Specific Gas Constant of Water (J/kg K)

I(1) = 0
I(2) = 0
I(3) = 0
I(4) = 0
I(5) = 0
I(6) = 0
I(7) = 0
I(8) = 0
I(9) = 1
I(10) = 1
I(11) = 1
I(12) = 1
I(13) = 1
I(14) = 1
I(15) = 2
I(16) = 2
I(17) = 2
I(18) = 2
I(19) = 2
I(20) = 3
I(21) = 3
I(22) = 3
I(23) = 4
I(24) = 4
I(25) = 4
I(26) = 5
I(27) = 8
I(28) = 8
I(29) = 21
I(30) = 23
I(31) = 29
I(32) = 30
I(33) = 31
I(34) = 32

J(1) = -2
J(2) = -1
J(3) = 0
J(4) = 1
J(5) = 2
J(6) = 3
J(7) = 4
J(8) = 5
J(9) = -9
J(10) = -7
J(11) = -1
J(12) = 0
J(13) = 1
J(14) = 3
J(15) = -3
J(16) = 0
J(17) = 1
J(18) = 3
J(19) = 17
J(20) = -4
J(21) = 0
J(22) = 6
J(23) = -5
J(24) = -2
J(25) = 10
J(26) = -8
J(27) = -11
J(28) = -6
J(29) = -29
J(30) = -31
J(31) = -38
J(32) = -39
J(33) = -40
J(34) = -41

n(1) = 0.14632971213167
n(2) = -0.84548187169114
n(3) = -3.756360367204
n(4) = 3.3855169168385
n(5) = -0.95791963387872
n(6) = 0.15772038513228
n(7) = -0.016616417199501
n(8) = 8.1214629983568E-04
n(9) = 2.8319080123804E-04
n(10) = -6.0706301565874E-04
n(11) = -0.018990068218419
n(12) = -0.032529748770505
n(13) = -0.021841717175414
n(14) = -5.283835796993E-05
n(15) = -4.7184321073267E-04
n(16) = -3.0001780793026E-04
n(17) = 4.7661393906987E-05
n(18) = -4.4141845330846E-06
n(19) = -7.2694996297594E-16
n(20) = -3.1679644845054E-05
n(21) = -2.8270797985312E-06
n(22) = -8.5205128120103E-10
n(23) = -2.2425281908E-06
n(24) = -6.5171222895601E-07
n(25) = -1.4341729937925E-13
n(26) = -4.0516996860117E-07
n(27) = -1.2734301741641E-09
n(28) = -1.7424871230634E-10
n(29) = -6.8762131295531E-19
n(30) = 1.4478307828521E-20
n(31) = 2.6335781662795E-23
n(32) = -1.1947622640071E-23
n(33) = 1.8228094581404E-24
n(34) = -9.3537087292458E-26

R = 461.526
Tstar = 1386
Pstar = 16530000#
Pi = P / Pstar
Tau = Tstar / T

temp = 0
For x = 1 To 34
Sum(x) = -n(x) * I(x) * (7.1 - Pi) ^ (I(x) - 1) * (Tau - 1.222) ^ J(x)
'Debug.Print "Sum( " & x & ") = " & Sum(x)
temp = temp + Sum(x)
Next x
'Debug.Print "Total = " & temp
'Debug.Print Pi
'Debug.Print Tau
SpecVolLiq = Pi * temp * (R * T / P)
End Function

Ouput from Spreadsheet = 0.00097118089402163
Output from VBA Function = 0.000971180896076593

Let me know if you have any ideas. Thanks!!

"Jerry W. Lewis" wrote:

The most likely explanation for discrpancies in that range is that you have
declared some variables "As Single". If the problem persists after changing
"Single" to "Double", then post your code.

Jerry

"Clint Schwartz" wrote:

I am writing some functions in vba after performing the calculations on a
spreadsheet. When I compare the results, it consistently appears that the
output from the vba function is not as accurate. It seems to lose accuracy
once you get to the 7th or 8th significant digit. Does anyone know why this
happens? Thanks!!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"