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
0
JohnM
12/1/2009 2:08:42 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
597 Views

Similar Articles

[PageSpeed] 23

On Mon, 30 Nov 2009 18:08:42 -0800 (PST), JohnM <john.mctigue@health.wa.gov.au>
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
0
Ron
12/1/2009 3:11:58 AM
On Dec 1, 11:11=A0am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Mon, 30 Nov 2009 18:08:42 -0800 (PST), JohnM <john.mcti...@health.wa.g=
ov.au>
> 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 =3D ((280 - (EDC - DOB)) \ 7) + (0.1 * ((280 - (EDC - DOB))
> >Mod 7))
>
> >Gest =3D 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. =A0What is causing this behaviour?
> >2. =A0Can I return the function value (as displayed in the immediate
> >window) to the worksheet cell?
>
> >Kind regards,
> >John McTigue
>
> I would suggest:
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Function Gest(DOB As Date, EDC As Date) As Double
> =A0 Gest =3D Round(((280 - (EDC - DOB)) \ 7) + _
> =A0 =A0 (0.1 * ((280 - (EDC - DOB)) Mod 7)), 1)
> End Function
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> Empirically, there seems to be an issue with how Excel displays a Single.=
 =A0It
> 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.
0
JohnM
12/1/2009 5:18:18 AM
On Mon, 30 Nov 2009 21:18:18 -0800 (PST), JohnM <john.mctigue@health.wa.gov.au>
wrote:

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

Glad to help.  Thanks for the feedback.
--ron
0
Ron
12/1/2009 12:52:36 PM
Reply:

Similar Artilces:

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

user created shapes non printing
I started have a problem with vision 2002 that I have not noticed before. When I create a new shape, by default, it assumes the non-printing properly under FORMAT � BEHAVIOR. Also if I group a set of "printing" shapes the group will become non-printing. Can I change this behavior? How are you creating the new shape? Also are you using layers in your document? -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Robert" <hammer_757@hotmail.com> wrote in message news:9ec427f7.0409231005.576...

Max of value from DataGroup2 within DataGroup1
Hello - I have a table with data as follows below. I am trying to build a query that will give me the record with MAX of specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum [DataGroup1]. ID TestNum TestFreq specDiffMax -------------------------------------------- 4889683 Test 1 1710 3.669998 5123289 Test 1 1710 2.882999 4817314 Test 1 1710 3.102001 5134007 Test 1 1710.2 3.573002 4896056 Test 1 1710.2 3.355 4914480 Test 1 1710.2 3.515999 4889685 Test 2 1710.4 3.333 4896057 Test 2 1710.4 3.450001 4914481 Test 2 ...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

How to Change Value of Active Control
I'm writing a routine to change the value of any Active Control to null. I can get the name of the control using ActiveControl.Name but I can't figure out the syntax to change the value of this control. I've tried assigning it to variables but variables aren't working in a: Forms![variable]![variable] = "" Help "Sondreli" <Sondreli@discussions.microsoft.com> wrote in message news:AA05A840-2AFE-4F98-ABE9-5D2DD17AE0B8@microsoft.com... > I'm writing a routine to change the value of any Active Control to null. > I > can g...

"All users" "Programs" create/modify shortcut from app...
Hi all, I've created two shortcuts into "Programs" folder for "All Users" It lets me to get them available for all user. The problem: Application running in "User" context needs to delete and re-create such links but it fails due to an "access denied" ... Settings correct permission to such links it starts working as well I've created links using the IShellLink/IPersistFile sehll interfaces. So, I actually need to have link under "programs" for "All Users" which might be modified by application running in "Users"...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

Validation Rule for field values
I have a database that includes a phone number field. I want to prevent a user from creating a new record that contains a phone number that is currently in a record that exists in the database.I am using a form for data entry into the table. Somehow I'd like for there to be a check to see if the phone number typed into the phone number field to be checked against existing phone numbers in the database.I've researched various sources but haven't been able to find the solution. I'm not familiar with VB so any replies that suggest using that will probably not work for me. Sorry, j...

How to view the code for excel built-in functions?
Is it possible? -For example the function PMT(). thanks. No, the code is compiled, so it would likely be less than useful anyway. About the best you can do is check out the equations used in Help (see "PV"). In article <OSU3OXOBGHA.1676@TK2MSFTNGP09.phx.gbl>, "serdar" <s@s.com> wrote: > Is it possible? -For example the function PMT(). > thanks. ...

Using XP Briefcase with money file
I put a copy of my money file into the laptop briefcase and I am able to keep them in sych for a while. For some reason I get locked out, the computer knows the file has been updated but it will not let the desk top file update the laptop file, I get the following message; "The source file can not be opened" "Check to see if the other program has the file opened, and close the file before updating the briefcase." Other files update just fine. When I get this message, I can't even drag a new copy of the Money file to my briefcase, I have to rename the file an...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

User status in Sharepoint 3.0
I see there is a user status that works with Office Communicator. Is there away to do this with out office Communicator or free software to do this? We are a small company with about 10 users. Nothing I'm aware of. This is built in integration with the OCS platform. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "todd" <todd@discussions.microsoft.com> wrote in message news:089333D7-F0D4-4994-87E3-74A387AE33D0@microsoft.com... > I see there is a user status that works with Office Communicator. Is > there > away to...

Exchange Features Tab in ADUC when using Windows 2000 SP3
Hi, I would like to run Active Directory Users and Computers with Exchange 2003 Admin tools I'm limited to Windows 2000 Prof/SP3 Everything works,except from the Exchange Features tab. When selecting this tab, ADUC hangs. Any ideas. (I have to stay on SP3!) br TN Terminal Services is always my first choice. On Wed, 13 Apr 2005 23:35:22 +0200, "newsMS" <t_n@trashcan.dk> wrote: >Hi, > >I would like to run Active Directory Users and Computers with Exchange 2003 >Admin tools >I'm limited to Windows 2000 Prof/SP3 > >Everything works,except...

How do I convert time (hh:mm) to value ($$) in Excel?
Would like to calculate cost of time. Eg. Cost for production down time per minute is $100. Says production doen for 3.5 hrs, what is formula shall I apply in order to generate the cost (in $$). =3.5*60*100 "ahfen79" wrote: > Would like to calculate cost of time. Eg. Cost for production down time per > minute is $100. Says production doen for 3.5 hrs, what is formula shall I > apply in order to generate the cost (in $$). =(3.5/24)*60*100 -- Regards Dave Hawley www.ozgrid.com "ahfen79" <ahfen79@discussions.microsoft.com> wrote in ...

Carriage Return/Line Feed Problem
Hi guys, I wrote some code that gets the HTML from a Web page and stores it in a buffer, then I display the buffer in a big multiline CEdit box. The problem is that on many pages I don't get new lines, I just get a thick solid verticle line kinda like this: || where it should return. Also, if I view the pages (view source) in Notepad they look fine. Most pages do that, but interestingly Microsoft pages do the proper new line and look great in my edit box. Maybe I have to replace all the \r with \r\n or something like that ? Does a \r\n still only take up 1 char ? I download the page in...

Using a variable for a chart limit
Since I got instant help on my last query, is there any way to use a cell reference as an axis minimum or maximum in format axis? It seems impossible, but there is a lot of experience out there. Thanks in advance. -- Vince F Hi, See Jon's information http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html Cheers Andy Vince F wrote: > Since I got instant help on my last query, is there any way to use a cell > reference as an axis minimum or maximum in format axis? It seems impossible, > but there is a lot of experience out there. Thanks in advance. -- Andy Pop...

How do I make door hangers using publisher?
Is this even possible? Mary Sauer has a website with a generous collection of goodies including door hangers. Take a look at just above George Washington's portrait (way down the page) at, http://msauer.mvps.org/publisher_projects2.htm -- Don - Publisher 2000� Vancouver, USA "medinapie" <medinapie@discussions.microsoft.com> wrote in message news:986B8199-C9B5-46CE-8F2F-C772BE5C21BC@microsoft.com... > Is this even possible? I have a sample on my web page. Scroll down -- on the left. http://msauer.mvps.org/graphics.htm Some cute ones here that you could mod...

uploading 2 or more mailboxes on 1 user account
I was able to reconnect 1 mailbox to 1 user account, but what i' looking is the way wherein i can reconnect 2 or more mailboxes into user account because instead of giving a 1:1 user account to eac person we will be replacing it and giving them an account per area Hence they already have their own personal account we will b deleting it; How can i put all their messages from their persona account into the single account that we wil be giving to them Hi, More than one mailbox per account is not possible in Exchange 200x. You can do that with Exchange 5.x Regards, -- Menko den Ouden ...

using program to record audio comment and compress audio to MP3
I can use VBA to record an audio comment into a Word file using Selection.InlineShapes.AddOLEObject ClassType:="SoundRec", FileName:="", _ LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _ "C:\WINDOWS\system32\sndrec32.exe", IconIndex:=0, IconLabel:="Wave Sound" BUT but by default the file is stored as a PCM at 44kHz and 16 bit stereo whcih is about 200kB per second Sound recorder (which is used to do the recording) allows me to manually convert this to a MP3 and a much smaller file e.g. 3kB per second Is there any way I can aut...

Getting an UnBound control value into a Table field??????
I have a form that calculates a production rate in "parts per hour". This form needs to be able to differentiate between Line work, Cutter work, Side work, and Blister work. I created a drop down list for selecting the "Study Type" as listed above. I then created a text box for each "study type". When I select the "study type" each text box checks to see if the selection applies to it and makes the calculation if it does apply. The code for the "Line" study is as follows: =IIf([StudyType]="Line",2700?/[SecondsPerPart])...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

Linking Drop-down list to worksheets
I need 20 dropdown list on the worksheet. some of drop-down lists has the same values. I need a list thats has one set of names and worksheet has another set of names. Here is a example want I need. LIST names Worksheet names Red - Black 770rb Black-White 770bw My list will have 15 items in each list. When user click on the color it would take them to that worksheet. What is best way to do this? ...

adding another user on same computer
Hi, I have made amew user on my computer so that the woman I live with=20 may use my computer without the danger of corrupting my files=20 accidentially. When we go to the dock on her side - and that is the side from which = I=20 am emailing you - there are two question marks where the icons for 'word = and excel would have been. How can I get this corrected? Just drag the question mark to the desktop and you will find them vanish. Next drag the Word and Excel programs to the dock and you will find it working properly. Anand P "poggi8@comcast.net" <anonymous@dis...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...