Data Type Function (Excel 2000)

[HUHandEH]

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

[HansV]

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.

[HUHandEH]

Thank you Hans.

[LegareColeman]

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>

[HUHandEH]

Thanks Legare