Maxifs() oddity

Anyone have any idea why maxifs() would get “stuck” on certain values that should be filtered out?

image description

Column A is raw numbers and need not be sorted, B contains the formula shown propagated down, C is just A=B for clarity. Sometimes a manual recalc will fix the issue, sometimes not. Currently running on Mac.


Why - most likely

I suspect some rounding error. You are using the full precision available to Calc. A compare operation is often implemented internally as a subtraction. I know it appears strange, but when you are at the precision limit, the distinction between < and <= may not be correctly determined by the simple subtraction.

Possible workaround

Do you really need this level of precision? Rounding to a moderate number of decimals should fix the issue if my suspicion is correct.

I suspect you are correct. Further fiddling revealed that subtracting 0.00000000000001 from the comparison value helped. I eventually went with multiplying column A by 1024^3 and converting to integer, and called it a day.