Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Data Type Function (Excel 2000)

    Thank you Hans.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    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
  •