average not eq average

In doing some calculations with Excel 2003 I found one can get two different answers as to the average of three numbers.

Anyone know why?

The workaround seems to code very explicitly and not use the first method... Or is there an 'answer' to what looks like a bug?

Code follows:

Function test()
' Apparently there is a bug here.
' Should not all three methods give the same result?
'Excel 2003

Dim a As Long
Dim b As Long
Dim c As Long
a = 466.439
b = 466.439
c = 489.5214

ActiveSheet.Cells(1, 1).Value = a
ActiveSheet.Cells(1, 2).Value = b
ActiveSheet.Cells(1, 3).Value = c

ActiveSheet.Cells(1, 5).Value = Application.Average(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 3))
ActiveSheet.Cells(1, 6).Value = "=average(A1:C1)"
ActiveSheet.Cells(1, 7).Value = (a + b + c) 3

End Function

I'd be up for a direct email if you've got ideas on this.

Comments

  • [b]ActiveSheet.Cells(1, 7).Value = (a + b + c) 3[/b]

    In VBA the backslash () performs an integer division - to get the average of non-integer numbers, change it to
    [b]
    ActiveSheet.Cells(1, 7).Value = (a + b + c) / 3[/b]
  • Very helpful, thanks; but there are still two issues.

    It seems Dim as Long needs to be Variant for precision or you get an integer result.

    The Application.Average approach gives an odd result; why is that?

    Code repeated here with more details on values calculated:

    Function test()
    ' Apparently there is a bug here.
    ' Should not all three methods give the same result?
    'Excel 2003
    ' actual mean is 474.1331

    Dim a 'As Long
    Dim b 'As Long
    Dim c 'As Long
    a = 466.439
    b = 466.439
    c = 489.5214

    ActiveSheet.Cells(1, 1).Value = a
    ActiveSheet.Cells(1, 2).Value = b
    ActiveSheet.Cells(1, 3).Value = c

    ActiveSheet.Cells(1, 5).Value = Application.Average(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 3)) ' 477.9802

    ActiveSheet.Cells(1, 6).Value = "=average(A1:C1)" '474.1331
    ActiveSheet.Cells(1, 7).Value = (a + b + c) / 3 ' 474.1331

    ActiveSheet.Cells(1, 8).Value = (a + b + c) 3 '474.0000 As you pointed out, an integer calculation.


    ActiveSheet.Cells(1, 9).Select ' same answer as 6 and 7 '474.1331
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-8]:RC[-6])"

    End Function
  • Long gives you a long integer variable - replace them with Single or Double, which can hold decimal values.

    Using Application.Average you have to specify all values to be averaged, not a range, so you are calculating an average of cells 1 and 3, not 1 to 3!


    Try this code now - they all give the same results (apart from the one with integere division)!

    [code]
    Dim a As Double
    Dim b As Double
    Dim c As Double
    a = 466.439
    b = 466.439
    c = 489.5214

    ActiveSheet.Cells(1, 1).Value = a
    ActiveSheet.Cells(1, 2).Value = b
    ActiveSheet.Cells(1, 3).Value = c

    ActiveSheet.Cells(1, 5).Value = Application.Average(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 2), ActiveSheet.Cells(1, 3)) ' 477.9802

    ActiveSheet.Cells(1, 6).Value = "=average(A1:C1)" '474.1331
    ActiveSheet.Cells(1, 7).Value = (a + b + c) / 3 ' 474.1331

    ActiveSheet.Cells(1, 8).Value = (a + b + c) 3 '474.0000 As you pointed out, an integer calculation.


    ActiveSheet.Cells(1, 9).Select ' same answer as 6 and 7 '474.1331
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-8]:RC[-6])"


    [/code]

  • Thanks! I never noticed any mention of the problem using ranges, which seemed intuitive.

    Is is not interesting how the cell reference method does work but the Range approach does not?

    Best regards.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories