# Thread: Data Type Function (Excel 2000)

1. ## Data Type Function (Excel 2000)

Hi all,

I am trying to get this function here to do what I want, for the most part it works fine, but what I don't understand is some of the values that it is producing. What is happening is at 5.4 the next number is 5.499999999, so from 3.5 to 5.4 works fine, but from 5.4 to 9.9 I have at least 15 decimal places....am I using the right data type? what I would like to have is 3.5 to 10.5. counting by .1

Thanks
Darryl
Public Function getPhValue() as double()
dim x(1 to 75) as double
dim i val as double
dim i as double
ival=3.5
For i = 1 to 75
x(i)=ival
debug.print ival
ival=ival+ 0.1
next i
getphvalue=x
end function

2. ## Re: Data Type Function (Excel 2000)

We use the decimal number system (base 10), but computers use the binary number system (base 2). The decimal number 0.1 cannot be represented exactly in the binary number system, so the computer uses an approximation. In many situation, you won't notice this, but if you add 0.1 repeatedly, the rounding errors accumulate until they become large enough to see them. Here is a possible workaround:

Public Function getPhValue() As Double()
Dim x(1 To 75) As Double
Dim i As Integer
For i = 1 To 75
x(i) = (i + 34) / 10
Debug.Print x(i)
Next i
getPhValue = x
End Function

This version uses the loop counter itself; since this is a whole number, the only rounding error occurs when dividing by 10, but the errors don't get a chance to accumulate.

3. ## Re: Data Type Function (Excel 2000)

Thank you Hans.

4. ## Re: Data Type Function (Excel 2000)

I think the following code will also do what you want, and it is a little easier to understand than Hans' solution.

<pre>Public Function getPhValue() As Double()
Dim x(1 To 75) As Double
Dim ival As Double
Dim i As Double
ival = 3.5
For i = 1 To 75
x(i) = ival
Debug.Print ival
ival = Round(ival + 0.1, 1)
Next i
getPhValue = x
End Function
</pre>

5. ## Re: Data Type Function (Excel 2000)

Thanks Legare

#### Posting Permissions

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