PDA

View Full Version : Basic variable type question (and maybe more)...



tizwoz123
01-13-2008, 01:05 PM
Just a quick question, I may have a couple more later but for now just one.

What's the difference between a single and a double variable? The definition I find is...


? single ≡ 32 bit (4 byte) floating point number between
-3.402823E38 to - 1.401298E-45 and
1.401298E-45 to 3.402823E38

? double ≡ 64 bit (8 byte) floating point number between
-1.79769313486231E308 to -4.94065645841247E-324
4.94065645841247E-324 to 1.79769313486232E308
Which brings about on question, What about zero and all the values close to it that seem to be outside the ranfe of both of these variable types?

For the function Z= X/(Y^2), if we stick x=70 and y=1.71 and treat the Z as double, we get the answer 23.93898964 whereas the correct answer is actually 23.93898977. How does this discrepancy come about? Which makes me question when we should use Double as opposed to Single? I just though double has more decimal places and would be more accurate with some calculations but clearly I'm wrong.

mikerickson
01-13-2008, 01:22 PM
If you are working with numbers whose absolute value is < 10^(-325), Excel is probably not the right platform for your use.

This routine shows the advantage of Double over Single.

Sub test()
Dim singVal As Single
Dim doubleVal As Double
singVal = CSng(70) / CSng(CSng(1.71) ^ 2)
doubleVal = CDbl(70) / CDbl(CDbl(1.71) ^ 2)
MsgBox singVal & vbCrLf & doubleVal
End Sub

Bob Phillips
01-13-2008, 03:24 PM
For the function Z= X/(Y^2), if we stick x=70 and y=1.71 and treat the Z as double, we get the answer 23.93898964 whereas the correct answer is actually 23.93898977. How does this discrepancy come about? Which makes me question when we should use Double as opposed to Single? I just though double has more decimal places and would be more accurate with some calculations but clearly I'm wrong.

By my calculation, it comes out as 23.9389897746315.

johnske
01-14-2008, 04:30 AM
Just a quick question, I may have a couple more later but for now just one.

What's the difference between a single and a double variable? The definition I find is...


Which brings about on question, What about zero and all the values close to it that seem to be outside the ranfe of both of these variable types?

For the function Z= X/(Y^2), if we stick x=70 and y=1.71 and treat the Z as double, we get the answer 23.93898964 whereas the correct answer is actually 23.93898977. How does this discrepancy come about? Which makes me question when we should use Double as opposed to Single? I just though double has more decimal places and would be more accurate with some calculations but clearly I'm wrong.If you want your answer as a double then all other variables used in that calculation must also be declared as double, otherwise you'll get an incorrect answer e.g. this will give the correct answer

Option Explicit
Sub a()
Dim X As Double, Y As Double, Z As Double
X = 70: Y = 1.71
Z = X / (Y ^ 2)
MsgBox Z 'gives correct answer (23.9389897746315)
End Sub

but because there are mixed data types this will give an incorrect answer

Option Explicit

Sub b()
Dim X As Long, Y As Single, Z As Double
X = 70: Y = 1.71
Z = X / (Y ^ 2)
MsgBox Z 'gives wrong answer (23.9389887065614)
End Sub

rlv
01-14-2008, 07:04 AM
Be wary of inadvertent type conversion. If I do the calculation and store the result as a single, then do a type conversion to double, I get the same erroneous value as you did (23.93898964)


Sub Test()
Dim S As Single
Dim D As Double, TC As Double
Dim V As Variant

S = 70 / 1.71 ^ 2
D = 70 / 1.71 ^ 2
V = 70 / 1.71 ^ 2

TC = S ' Implied type conversion

Debug.Print "S = " & S
Debug.Print "D = " & D
Debug.Print "V = " & V
Debug.Print "XL = " & ActiveSheet.Range("A1") 'calc in spreadsheet
Debug.Print "Type Conversion TC (rounded to 8 places) = " & Format(TC, "0.00000000")

End Sub
Results:

S = 23.93899
D = 23.9389897746315
V = 23.9389897746315
XL = 23.9389897746315
Type Conversion TC (rounded to 8 places) = 23.93898964