I'm using Microsoft Excel for Macintosh 2011 and have a spreadsheet of start and stop time spent on activities with some time differences between start and stop times that are converted into total hours in two different paths. In one path, the whole column of time differences is summed all at once. In a separate path, a series of subsets of the column for adjacent rows is summed into a different column (all corresponding to a single day), which is itself summed to theoretically get the same sum as the first sum.
I am subtracting these two sums and if the result is non-zero, I have conditional formatting to color it red to alert the user to an error in their spreadsheet and that the two paths/methods for summing do not agree.
My formula has been working well, however, as my spreadsheet has now grown to around 1,500 rows, I am now seeing round-off errors where the two different paths to sum up the times get slightly different values that differ by 0.000000000000909494701773 hours and the cell is being highlighted red for being non-zero.
How can I avoid the round-off drift that is occurring between these two paths to compute the same sum? I have found that I can temporarily work around the issue by increasing one entry in the sum by 1 second and the difference returns to zero again and the cell goes green again.
Is this a known issue with Excel? Can I cause my zero compare conditional formatting to allow more slop in the comparison? Can I force the two paths to compute the sums to agree without adding in a second to one of the times?
Best Answer
To my knowledge you can't avoid the rounding differences, however, you can adjust your conditional formatting to accept some slop. Select "Use formula to determine which cells to format" and try something like this: