1

I wrote a macro which as a tiny task on the side also calculates the average of around 39000 different values. I noticed that using WorksheetFunction.Average and calculating the average "step-wise" yield different results, but only at the 15th digit after the decimal point. By calculating "step-wise" I mean adding up each value to a total_sum variable, counting the amount of values in another variable and then dividing the former by the latter. The 15th digit after the decimal point might be considered negligible but I find it unsettling nonetheless. Shouldn't those two values be exactly the same? They are when I use less values and as the macro might be applied on far more values than 39000 (100k+), I'm worried the error might increase.

So my questions are: What could cause the difference and more importantly which method is more precise?

What I tried was to declare all variables in the "step-wise" calculation as Variant to avoid using the wrong data type in any of those steps.

Thank you very much for your help!

Nat100
  • 11
  • 2
  • 5
    Maximum level of precision excel can handle is 15 digits. – Tim Williams Sep 16 '19 at 19:52
  • 2
    ...15 significant figures to be more precise (ahem!) https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel – Tim Williams Sep 16 '19 at 20:04
  • 1
    And anyway, it's highly unlikely that your _input_ data is accurate to 15sf, so any variance at the 15th digit is meaningless. – chris neilsen Sep 16 '19 at 20:36
  • Floating-point error will compound as you carry it into each subsequent operation - I'd consider `WorksheetFunction.Average` as "more accurate", but then again if the 15th digit has any meaning, then the problem seems to be the working order of magnitude. – Mathieu Guindon Sep 16 '19 at 20:39
  • 1
    If for some reason, you require increased precision, the VBA Decimal data type can give you about 28 digit precision. – Ron Rosenfeld Sep 16 '19 at 20:54
  • Thank you very very much to everyone for all the great answers! – Nat100 Sep 23 '19 at 12:45
  • @MathieuGuindon why would you consider WorksheetFunction.Average as "more accurate". Wouldn't this function also use some sort of step-wise operation and therefore come with an error margin? – Nat100 Sep 23 '19 at 12:49
  • It comes with an error margin, yes; but if one is calculated natively and the other is calculated in VBA and the two aren't the same, then I'll take the native value as accurate. – Mathieu Guindon Sep 23 '19 at 12:52
  • And a follow-up question: Is there a method that is more recommended for reasons other than accuracy? E.g. performance, stability or lower chance of stupid mistakes when coding? – Nat100 Sep 23 '19 at 12:54
  • @MathieuGuindon okay makes sense! Thank you :) – Nat100 Sep 23 '19 at 12:55

0 Answers0