Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting a single-precision value (Excel 2000, SR1)

    Hi Folks,

    I have some code that declares a variable as a single-precision number, then performs a simple arithmetic operation to calculate the value. Later, the value gets applied to a cell in the worksheet.

    The value, as displayed in the cell, shows only two decimal places, which is what I expect, given the input values. However, in the formula bar, the value is shown to 14 places, not all of which are zeros. This happens, despite the fact that the cell has General number format.

    Here is the code:
    <pre>Dim TheValue as Single
    TheValue = Center - (((Steps - 1) / 2) * Step)
    MsgBox TheValue
    Range("A1").Value = TheValue
    </pre>


    Center (single) = 0.00
    Steps (integer) = 15
    Step (single) = 0.15

    The MsgBox does indeed display -1.05, despite not having any special formatting applied. That is, there is no need to use the Format function.

    Even if I use the Round function to try rounding TheValue to two decimal places, the result is the same.

    What is happening? Not critical, but it seems weird. Thanks in advance!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formatting a single-precision value (Excel 2000, SR1)

    Jim,

    This phenomenon is a result of the way binary numbers are stored and rounded. Are you sure the result shows as -1.05 when formatted as general. Try the following code, which substitutes the values given by you for the variables :

    Dim dblValue As Double
    Dim sngValue As Single
    dblValue = 0 - (((15 - 1) / 2) * 0.15)
    sngValue = 0 - (((15 - 1) / 2) * 0.15)
    [A1:A3].NumberFormat = "General"
    [a1] = dblValue
    [a2] = sngValue
    [a3] = dblValue - sngValue

    On my system, the 'single' value shows as -1.049999952 in a cell formatted as general. (up to 14 decimal places in the formula bar, as you say). The double shows 1.05 (the correct value) in the cell and the formula bar.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting a single-precision value (Excel 2000, SR1)

    Hi Andrew,

    Yes, I am sure the cell was formatted as General.

    On my system, I get the same results as you do with the code you posted. Thanks!

    So, if I don't want the mysterious symptom, I should declare the varables in question as double precision, eh? Things like this remind me, a cumputer is just a huge abacus when you get right down to it...

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting a single-precision value (Excel 2000, SR1)

    Jim,

    I can only confirm Andrew's post. Be careful when using the 'Single' variable type, especially when you do arithemetic calculations with very small and very large numbers. If precision is required, always use the Double data type.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting a single-precision value (Excel 2000, SR1)

    Of course, I agree.

    In this case, the arithmetic is quite simple, and extra precision is definitely not needed (and not wanted, for that matter). However, floating point is necessary, limiting the choices to single or double. I thought single would be sufficient, but the unwanted "precision" appeared. Double seems to work just fine (strangely).

    Thanks! I always enjoy your posts.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting a single-precision value (Excel 2000, SR1)

    FYI: Note that numerical data in Excel spreadsheets are always in 'double' precision. You can check that by using the vartype command in VBA.

    <pre>Sub test()
    If VarType(ActiveCell.Value) = vbDouble Then
    MsgBox "Double precision"
    End If
    End Sub
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •