Conditional formatting Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2692635%22%20slang%3D%22en-US%22%3EConditional%20formatting%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2692635%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20data%20in%20Microsoft%20Excel%20that%20I%20use%20conditional%20formatting%20on.%26nbsp%3B%20To%20ensure%20that%20all%20entries%20for%20a%20particular%20day%20are%20referenced%20in%20a%20daily%20total%20cell%2C%20I%20have%20set%20up%20a%20totals%20cell%20for%20the%20daily%20totals%20and%20have%20it%20conditionally%20formatted%20to%20flag%20red%20if%20the%20total%20there%20is%20different%20then%20the%20weekly%20total%20shown%20(See%20the%20screen%20shot).%26nbsp%3B%20However%2C%20on%20some%20occasions%2C%20for%20reasons%20I%20am%20stumped%20to%20find%2C%20the%20system%20will%20flag%20the%20daily%20column%20red%20when%20the%20totals%20do%20in%20fact%20match.%26nbsp%3B%20I%20have%20checked%20the%20formatting%20of%20the%20cell%2C%20the%20same%20on%20both.%26nbsp%3B%20I%20have%20checked%20for%20leading%20and%20trailing%20zeros%2C%20there%20are%20none.%26nbsp%3B%20Both%20are%20exactly%20the%20same%20equation%20set%20ups%2C%20they%20are%20a%20%3DSUM(the%20cells%20immediately%20above%20for%20the%20entire%20week).%26nbsp%3B%20I%20have%20deleted%20and%20reset%20the%20conditional%20formatting%20for%20this%20particular%20cell%2C%20no%20change.%26nbsp%3B%20I%20am%20uncertain%20what%20else%20can%20be%20done%20to%20possibly%20fix%20this%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2692635%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2692698%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2692698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1139513%22%20target%3D%22_blank%22%3E%40MrsBenson%3C%2FA%3E%26nbsp%3BCould%20be%20a%20rounding%20error.%20Though%20difficult%20to%20diagnose%20with%20seeing%20the%20formulas%20an%20underlying%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2692713%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2692713%22%20slang%3D%22en-US%22%3EWith%20a%20rounding%20error%2C%20would%20that%20be%20something%20that%20lays%20in%20the%20formatting%20of%20the%20cells.%20At%20this%20time%20all%20of%20the%20cells%2C%20the%20ones%20being%20used%20for%20the%20sum%20as%20well%20as%20the%20sums%20themselves%2C%20are%20all%20formatted%20using%20the%20custom%20format%20of%20%22%5Bh%5D%3Amm%22%3C%2FLINGO-BODY%3E
New Contributor

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.

6 Replies

@MrsBenson Could be a rounding error. Though difficult to diagnose with seeing the formulas an underlying data.

With a rounding error, would that be something that lays in the formatting of the cells. At this time all of the cells, the ones being used for the sum as well as the sums themselves, are all formatted using the custom format of "[h]:mm"

@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.

@MrsBenson 

Formatting doesn't affect values, that only defines how to show them.

What throws me with this is the same conditional formatting is used repeatedly throughout the file on similar groupings without issues. It is just this one grouping, and just with this one total. If I change one of the values to change the overall total, even by 1 minute, it resolves and the conditional formatting does not color the cell.

@MrsBenson 

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.