User defined function returns imprecise value when used in worksheet
 

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

User defined function returns imprecise value when used in worksheet
 

On Mon, 30 Nov 2009 18:08:42 -0800 (PST), JohnM
wrote:

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

User defined function returns imprecise value when used inworksheet
 

On Dec 1, 11:11*am, Ron Rosenfeld wrote:
On Mon, 30 Nov 2009 18:08:42 -0800 (PST), JohnM
wrote:





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- Hide quoted text -

- Show quoted text -

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

User defined function returns imprecise value when used in worksheet
 

On Mon, 30 Nov 2009 21:18:18 -0800 (PST), JohnM
wrote:

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

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