Rounding Issue when using Average

[gruntingmonkey]

In cell A1 I have the formula =AVERAGE(A2:A65)

I had an issue that some conditional formatting wasn't working and then noticed that the issue was due to "out of place" decimal points. I have resolved the issue by using roundup in the formula however I'd like to understand why excel, using average, adds/removes a fraction of a %.

The example below shows that for 0.7 - if you average it over 63 cells it adds up to 0.700000000000001 .Averaging it 62 times shows correctly
However for 0.8 it only takes 54 averages to "break". Average 53 times and it shows as .800000000000000

 

[Fluff]

It's down to floating point arithmetic. Floating-point arithmetic may give inaccurate result in Excel - Office

 

[gruntingmonkey]

It's down to floating point arithmetic. Floating-point arithmetic may give inaccurate result in Excel - Office

This is amazing - Fluff, I'm pretty sure that you could answer ANY question I could ever put to you. Don't suppose you know how I could get a better nights sleep? Haha.

Thank you once again, for giving me a leg up on my learning journey.

 

[Fluff]

You're welcome & thanks for the feedback.