Unable to make match certain values

[ChristiaanV]

I'm having a wierd problem with Excel 2000.

I have a table with two columns that matches a certain delinquency rate to a borrowing rate. So column one has values of 0 to 100% in 1% incraments. Column two has different borrowing rates ranging from 75% to 0%.

I have another table on which delinqency rates are derived from different calculations. I then have formulas where I round up the derived calculations to the nearest 100th place, and than use that delinquency rate to match my table and get a borrowing rate.

This works fine, except that values that round to .21 (21%) and .24 (24%) result in #N/A errors when I do the lookup, whereas other values do not (though I have not tested every possible value). If I replace the derived value with a typed value, it works. In other words, if I point to a cell that has a value of .24 from a rounding a calculation and plug it in to my lookup formula - I get the error. If the same formula points to a hard-coded value of .24, it works.

I've used index/match functions and vlookup for the lookups, and I've used roundup and truncate for the rounding, and I get the same error - those particular values can't be matched. What's going on?

Thanks in advance for any help.

[shg]

Post a sanitized workbook that illustrates the problem.

[ChristiaanV]

Ok. Attached is a sanitized, simplified sheet.

The "BorrowingRate" sheet shows the problem. Only cell K4 should have an NA error, because we are missing some data for that month. The other cells should all have a value, which comes from taking calculated values from the "CPI" sheet, and looking up values on the "CPIRate" sheet (I use some named formulas, btw).

So the "NY" value on the first row should have a result: 71% to be exact. Below this table, I added some extra stuff to make comparison easier. The lookup vs calculated rows at the bottom have the same formulas - they just point to a different variable, located in the "Calculated" and "Hard-Coded" rows above.

Let me know if this example is not clear.

Thanks.

[shg]

Curious; ROUNDUP(x, 2) does not always return a value exactly equal to the result of ROUND(x, 2) when both should have the same result (they are different in the least significant bit).

Three possible solutions:

o Replace ROUNDUP with ROUND

o Wrap ROUNDUP with ROUND, i.e., ROUND(ROUNDUP ..., 2), 2)

o Recognize that CPIRate is just 0 to 100% in 1% steps, and replace the formula with

=INDEX(AdvanceRate, CEILING(INDEX(CPITable,MATCH($A6,CPIDate,0),MATCH(K$3,CPIHead,0) )* 100, 1) + 1)

Edit: For anyone curious,

21% manually entered in a cell is 3F CA E1 47 AE 14 7A E1

=ROUND(0.207, 2) is               3F CA E1 47 AE 14 7A E1

=ROUNDUP(0.207, 2) is             3F CA E1 47 AE 14 7A E2

[ChristiaanV]

Thanks! I was beginning to go crazy....