Aug 27 2021 07:41 AM
I have data in Microsoft Excel that I use conditional formatting on. To ensure that all entries for a particular day are referenced in a daily total cell, I have set up a totals cell for the daily totals and have it conditionally formatted to flag red if the total there is different then the weekly total shown (See the screen shot). However, on some occasions, for reasons I am stumped to find, the system will flag the daily column red when the totals do in fact match. I have checked the formatting of the cell, the same on both. I have checked for leading and trailing zeros, there are none. Both are exactly the same equation set ups, they are a =SUM(the cells immediately above for the entire week). I have deleted and reset the conditional formatting for this particular cell, no change. I am uncertain what else can be done to possibly fix this issue.
Aug 27 2021 07:55 AM
@MrsBenson Could be a rounding error. Though difficult to diagnose with seeing the formulas an underlying data.
Aug 27 2021 07:57 AM
Aug 27 2021 08:10 AM - edited Aug 27 2021 08:11 AM
@MrsBenson Don't know. Time entries are in fact numbers with quite a few decimals. Summing 5 summed values of 6hrs and 38min that add up to 33hrs and 10min may give a slightly different total than summing 10 individual. But perhaps it's not that at all.
Aug 27 2021 08:12 AM
Formatting doesn't affect values, that only defines how to show them.
Aug 27 2021 08:15 AM
Aug 27 2021 09:52 AM
Excel, as any other software, internally works with bits and number of bits involved into operations is limited. Thus we have floating point errors Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs . Great sample is here Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” | Micr... , =1*(.5-.4-.1) is not exactly equal to zero.
Taking into account time in Excel is just decimal part of the number (e.g. 01:00:00 = 1/24) you shall take care about precision. In one case time aggregation could give exact result you expect, in other case it could differ on something like 1e-27. You never know.