Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|