I have a user defined function (UDF) which takes two inputs. Both inputs have been declared as type Double
. However, while the input has a large number of decimal places, once passed to the UDF, the number of decimal places gets truncated.
Curiously, I cannot seem to reproduce this behaviour in a clean workbook (ie, I wonder if there is something going on with this particular workbook...).
The (simplied) UDF is below.
Function Wage(HourlyWage As Double, HoursWorked As Double) As Double
Wage = HourlyWage
End Function
as well as a screenshot of what is happening.
The formula bar shows the UDF call. It is taking columns C and D as inputs. HourlyWage is 25.30364372+ in column C, but once passed to the UDF Wage, the HourlyWage is truncated to 25.30360000 despite being declared as type Double
.
I understand general points about floating point precision, but this surely applies equally to the input in column C as it does to the UDF (indeed, column C is in actual fact the output of another UDF which has been declared as type Double
).
Why can't decimal numbers be represented exactly in binary?
Why are floating point numbers inaccurate?
Additional information about the EXCEL workbook.
- I am using Windows 7 Professional and Microsoft Office 365 ProPlus 64-bit version.
- The workbook is an .xlsb format. I have also saved as .xlsm and it does not fix the issue.
- I cannot reproduce in a clean workbook.
- The workbook contains a large number of interrelated user defined functions (maybe 40)
- The workbook contains 11 sheets. 5 of these are datasheets with a number of parameters, but I wouldn't have said large in the grand scheme of things (around 100 rows and 30 columns). 3 sheets have a largeish number of UDF and calculations (again, 100 rows by maybe 40-50 columns)
- I wouldn't have said the workbook is large. It is 369Kb (.xlsb) and 525KB (.xlsm)
- However, the workbook is taking an unexpectedly long time to recalculated when input parameters are changed (say around 10 seconds which I find surprising as it doesn't seem a lot of calculations in the grand scheme of things)
Any ideas on how to fix this? The loss of precision is causing me issues...