Input error to embedded excel using the number "1.5" and "in" unit

[Bartz87]

I have an embedded excel, add an input variable and pass it to excel. In excel, in cell A1, I made a formula that uses the passed value as an argument in a Vlookup function to lookup in a table. Please see attach Mathcad file. But I find it strange that every time I input the value "1.5in" in variable 'd1' in Mathcad worksheet and passed it to excel, the Vlookup formula gets '#N/A' error. The passed value is obcourse a number because I divide the unit 'in' to the excel input. The same error occurs when I input "3in". But the rest of the values (1in, 1.5in, 2in, etc.) works fine. Also when I change the unit to "1.5ft" as shown on the second input 'd2' that goes to cell B1, the result in cell B2 works fine. Is this a bug in Mathcad?

[AlvaroDíaz]

Hi Neptali.

Use round before send, like the figure, or after, in excel.

Best regards.

Alvaro.

[Werner_E]

You experience roundoff errors

And this value can't be found by VLOOKUP in the first column of the excel sheet and because the last parameter of VLOOKUP is 0 (=FALSE), Excel looks for an exact match the therefore throws the error.

One way to deal with is, as Alvaro suggested, to round off the value to an appropriate number of decimals - either in Mathcad or Excel.

Another approach, which I would prefer, is, to modify the last argument of the VLOOKUP command in the EXCEL cell and change it from 0 (FALSE) to 1 (TRUE) or delete that argument alltogether, as TRUE is the default value anyway. The values in the data range have to be sorted in ascendant order for this to work.

Regards

Werner