gruntingmonkey
Active Member
- Joined
- Mar 6, 2008
- Messages
- 434
- Office Version
- 365
- Platform
- Windows
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
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