1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Some bizarre maths in Excel VBA

Discussion in 'HTML, Graphics & Programming' started by Anchundan, 24 Apr 2020.

  1. Anchundan

    Hitman

    Joined: 26 Feb 2004

    Posts: 956

    Location: China (Qinhuangdao)

    Actually, this originated in some old VB6 code that someone found an error in, and asked me to have a look at, but I've reproduced it in VBA for simplicity.

    I thought the problem was due to defining some variables as 'Single' data type, but now I'm not so sure.

    Code:
    Sub BasicError()
    
    Dim XCofG As Single
    Dim Weight As Single
    Dim Moment As Single
    
        XCofG = 331250
        Weight = (135.93 + 135.11 + 134.39) / 3
        Moment = XCofG * Weight
        Debug.Print "XCofG: " & Format(XCofG, "0.0000000")
        Debug.Print "Weight: " & Format(Weight, "0.0000000")
        Debug.Print "331250 x 135.1433: " & Format(331250 * 135.1433, "0.0000000")
        Debug.Print "Moment: " & Format(Moment, "0.0000000")
    
    End Sub
    Edit: I should have added the results of that code:

    XCofG: 331250.0000000
    Weight: 135.1433000
    331250 x 135.1433: 44766218.1250000
    Moment: 44766230.0000000


    Because the variables are defined as Single, Weight becomes 135.1433, XCofG is 331250 and moment should therefore be 135.1433 x 331250 = 44766218.125, but because it is also Single, and Single numbers can only have 7 significant figures, it should be rounded to 44766220, but it's rounded to 44766230, and I can't understand why?

    It's almost like when it calculates Moment, it is not considering the Weight as a Single number, but using the full number to make the calculation, and then reduces Moment to 7 significant figures.
     
    Last edited: 24 Apr 2020
  2. Flanders

    Hitman

    Joined: 2 Nov 2004

    Posts: 571

    Location: London, UK