User defined function returns imprecise value when used in worksheet

[JohnM]

I want to create a function to return the gestational age of babies on
their date of birth (DOB) with reference to the due date (the
estimated date of confinement or EDC).

The function I have at the moment is:

Function Gest(DOB As Variant, EDC As Variant) As Single

'Returns the gestation in the form x.y where x is the number of
completed weeks
'and y is the additional days, which can range from 0 to 6.
'
'The calculation is based on the DOB and the EDC, and assumes a normal
pregnancy
'duration of 280 days, with the start being day 0 (not day 1).

Dim Gestation As Double

Gestation = ((280 - (EDC - DOB)) \ 7) + (0.1 * ((280 - (EDC - DOB))
Mod 7))

Gest = Gestation

End Function

When I test this within the immediate window I get the result I expect
eg:
?gest(#14-Jan-01#,#14-Mar-01#)
31.4

However, when using the function in a worksheet with the same DOB and
EDC I see a value of 31.3999996185302 (when I use formula auditing).
I had wanted to compare my calculation to the value displayed in an
existing 'gestation' column.

1. What is causing this behaviour?
2. Can I return the function value (as displayed in the immediate
window) to the worksheet cell?

Kind regards,
John McTigue

 

[Ron Rosenfeld]

I want to create a function to return the gestational age of babies on
their date of birth (DOB) with reference to the due date (the
estimated date of confinement or EDC).

The function I have at the moment is:

Function Gest(DOB As Variant, EDC As Variant) As Single

'Returns the gestation in the form x.y where x is the number of
completed weeks
'and y is the additional days, which can range from 0 to 6.
'
'The calculation is based on the DOB and the EDC, and assumes a normal
pregnancy
'duration of 280 days, with the start being day 0 (not day 1).

Dim Gestation As Double

Gestation = ((280 - (EDC - DOB)) \ 7) + (0.1 * ((280 - (EDC - DOB))
Mod 7))

Gest = Gestation

End Function

When I test this within the immediate window I get the result I expect
eg:
?gest(#14-Jan-01#,#14-Mar-01#)
31.4

However, when using the function in a worksheet with the same DOB and
EDC I see a value of 31.3999996185302 (when I use formula auditing).
I had wanted to compare my calculation to the value displayed in an
existing 'gestation' column.

1. What is causing this behaviour?
2. Can I return the function value (as displayed in the immediate
window) to the worksheet cell?

Kind regards,
John McTigue

I would suggest:

======================
Function Gest(DOB As Date, EDC As Date) As Double
Gest = Round(((280 - (EDC - DOB)) \ 7) + _
(0.1 * ((280 - (EDC - DOB)) Mod 7)), 1)
End Function
======================

Empirically, there seems to be an issue with how Excel displays a Single. It
undoubtedly has to do with the level of precision available and the fact that
decimal digits can not always be accurately expressed in binary notation.
--ron

 

[JohnM]

I would suggest:

======================
Function Gest(DOB As Date, EDC As Date) As Double
  Gest = Round(((280 - (EDC - DOB)) \ 7) + _
    (0.1 * ((280 - (EDC - DOB)) Mod 7)), 1)
End Function
======================

Empirically, there seems to be an issue with how Excel displays a Single. It
undoubtedly has to do with the level of precision available and the fact that
decimal digits can not always be accurately expressed in binary notation.
--ron- Hide quoted text -

- Show quoted text -

Thank you for the suggestion, Ron. It works just as I wanted.

 

[Ron Rosenfeld]

Thank you for the suggestion, Ron. It works just as I wanted.

Glad to help. Thanks for the feedback.
--ron