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

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.