Anyone have any idea why maxifs() would get “stuck” on certain values that should be filtered out?
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 7.0.4.2 on Mac.
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.
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.
Thanks