problem with MOD in lookup

[Lee Harris]

In a certain column I have a value

=M2/L2 let's say, but I need it to be in units of 0.05 so I do

=M2/L2-MOD(M2/L2,0.05)

this seems to give the right answers.

However when I use this value in a lookup table, with the number being the
value on the row I need out of the table, many results come back as #N/A

Now, when I either type in THE SAME VALUE ! or do a copy/paste
special/values only with the numbers that I've trimmed to be in units of
0.05, suddenly the lookups work properly

I am thinking that the MOD function is perhaps doing something funny here
but I'm not sure what

any ideas?
tks in advance

 

[Jerry W. Lewis]

0.05 (and most other terminating decimal numbers) has no exact
representation in binary. You might be able to get things to work by
rounding everything in sight, but a safer approach would be to
restructure the problem so that all numbers are integers, and therefore
exactly representable.

Jerry

 

[BrianB]

It is more likely that the problem is related to he usual VLOOKUP()
problem that both sets of cells containing the numbers have to be
formatted the same. Not just that, but we also have to select the
newly formatted column(s) and use Data/Text to Columns/Finish.

Regards
BrianB
============================================================

 

[Myrna Larson]

I've never heard that formatting affects VLOOKUP. For example, if I have a table with dates in
the left column (dates are numbers in Excel), formatted as mm/dd/yyyy, and in A1 a date
formatted as d-mmm-yyyy, VLOOKUP works just fine.

The usual reason for a NA result is that the last argument was specified as 0, to require an
exact match.

Lee might try the MROUND function for the rounding, i.e. =MROUND(M2/L2,0.05), but this will
round 3.03 to 3.05 rather than 3.00.

 

[Jerry W. Lewis]

And the difficulty with requiring exact matches is that the divisor
cannot be represented exactly, so there will almost never be an exact
match because of binary approximations. For example if you copy
=ROW()/20 down a column, only seven (.05, 0.1, 0.2, 0.4, 0.8, 1.6, 3.2)
of the first hundred multiples of 0.05 will have mod(x,0.05)=0.

Hence my earlier suggestion to restructure the problem to work with
integers. Otherwise, you would need to do something like
mod(round(mod(x,0.05),10),0.05) to get things to work reliably.

Jerry

 

[Myrna Larson]

I probably wasn't clear. I suggested the problem might be due to requiring an exact match. If
that's the case, it can perhaps be solved by NOT doing so. But that may not work, either, if the
floating point "error" returns a result that is a bit low instead of a bit high.