Email discussion lists for the UK Education and Research communities

## EXCEL-VB-DISCUSS@JISCMAIL.AC.UK

#### View:

 Message: [ First | Previous | Next | Last ] By Topic: [ First | Previous | Next | Last ] By Author: [ First | Previous | Next | Last ] Font: Proportional Font

#### Options

Subject:

Re: Accuracy of Variables

From:

Excel Visual Basic for Applications.

Date:

Wed, 27 Jul 2011 10:54:46 -0600

Content-Type:

text/plain

Parts/Attachments:

 text/plain (34 lines)
 ```At 17:15 +0100 7/27/11, James Button wrote: >If you are calculating using Float, then fractional parts are held as negative powers of 2 >If you want to put the value as a decimal value into a cell, then you need to convert that value before you put it into the cell >as in decimal_or Currency_variable = round_float > >I also noted (Well Patrick O'Beirne pointed me to my incorrect assumption) that the problem persists with Trunc, and can then confound all by having the value as displayed rounded if the display format decimal place setting is less than the value. > >BASIC approach - > >Keep (Long)Integer, Decimal/currency and float number holding within Excel (and VBA) separate > >Also - if the value may be float (as in the result of a division) then do not test for = value, but test for >ABS(variable-testvalue)<0.0000001 (With enough zeros for whatever accuracy you want/need/can get away with!) > >JimB > >----- From Original Message ----- >From: Fen Scott >To: [log in to unmask] >Sent: Wednesday, July 27, 2011 5:00 PM >Subject: Accuracy of Variables > > >Now – I’m getting some issues with accuracy. As an example, when TotVA = 2 and CountVA = 10, AveVA is calculated as 0.2 (break mode lets me see this), but when it writes the info to the cell it writes 0.200000002980232 It looks as though you're dealing with 9 significant digits though it might be more if Excel VBA converts numerator and denominator to double or extended floats before division. There is a really fundamental limit to double floats at approximately 14 decimal digits which comes up when the values are truncated to 52 bits in accordance with the IEEE floating point format. Some fractions - negative powers of 2 and sums of them - can be exact. Otherwise they are repeating fractions like 1/3 = .33333..... in decimal. 1/10 (10) = .00011001100,,,forever (base 2) is a real PITA because of its wide use in currency. Intermediate results probably have an extra 16 bits as type extended but they cannot be brought back into a worksheet for formatting by Excel. -- --> From the U S of A, the only socialist country that refuses to admit it. <--```