Maximum SUM in a column with a criterion

[Glenn Kennedy]

This arose today when I was helping someone here. I am confused.... Any explanations on offer????

This is a standard formula used to return the maximum SUM in a column with one criterion:
=IFERROR(1/(1/LARGE(INDEX(SUMIFS(C3:C10,A3:A10,"A",B3:B10,B3:B10),0),1)),"")


and this returns the corresponding value in another column.
=IFERROR(INDEX(B3:B10,MATCH(F4,INDEX(SUMIFS(C3:C10,A3:A10,"A",B3:B10,B3:B10),0),0)),"")

Refer to the file. Blocks A, B, C and D are EXACTLY the same data/formulae, with the sole exception that the values for Joe (the bloke with the highest SUM value) vary. The values increase from left to right. One 3/4 occasions Joe's name is returned correctly.

Block A, it works.. Block B, it works, Block C... it doesn't (!!!), Block D it starts working again. In block E, I found that a 1/(1/SUMIFS) fixed it and Joe's name is, once again, returned.

I have been travelling for a couple of days and I'm tired and a bit grumpy. I cannot see why this is failing in Block C and it's beginning to annoy me. Does anyone know why Block C has failed?

[Fluff13]

Using

Please Login or Register  to view this content.

in T4 also fixes it, so can only assume it's to do with floating point errors.

[AliGW]

Some sort of rounding error. This fixes it:

=IFERROR(ROUND(1/(1/LARGE(INDEX(SUMIFS(Q3:Q10,O3:O10,"A",P3:P10,P3:P10),0),1)),0),"")

[Glenn Kennedy]

Can you have floating point problems with integers?? No reason why not, I guess since they're double reciprocals.. I put that bit in to avoid null responses in the Post that I was replying to. I shall play with this, as I have my doubts. Back later...

[Fluff13]

As a simple test, if you use
=(200000-T4)*10^10
it returns 0.29
It appears that match looks at the whole value & not just the 1st 15 significant digits.

[Bo_Ry]

I also think this is caused by floating-point as 1/20000 is not an integer

There are many numbers that cause this eg: 98, 99

I set this
=MATCH(1/(1/C14),C14,0) Exact match
=MATCH(1/(1/C14),C14,1) Less than
=MATCH(1/(1/C14),C14,-1) Greater than

98 gives N/A for Exact match and Greater
99 gives N/A for Exact match and less

But this only happens with LOOKUP Function.

=1/(1/C14)=C14 give True on every Integer