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

1. 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. 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. 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. 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. 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. 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
•