Loosing digits in a total variable declared as single?

Question

I have something really weird.

I coded a bunch of VBA codes for a Workbook and there is this specific procedure where I do on screen reading from another sheet where the user entered data.

This reading is through a loop based on how many data the user entered.

I take each data, display them on another sheet, same workbook.

Sum each of them to a Total variable declared as single

then when the loop is finished I display that total under the column where all these data are displayed

If I do a manual sum() of all those data, there is a difference with this Total down to the 4th digit and I don't know why

Here is the code (I removed everything not related and only kept everything about those 2 variables involved in this problem):

Dim Total_Client_Unit_Task As Single

Dim TEMP_Unit_Task As Single

Total_Client_Unit_Task = 0

For i = 1 To HowManyExtractedClients

. . . 

	 

' reading each data the user entered in that sheet 

TEMP\_Unit\_Task = Worksheets(Tool\_Creation\_Step6\_TAB).Range(Coord\_Target\_Usage\_Task).Offset(i - 1, 0).Value 

. . .   TEMP\_Unit\_Task might have its value changed depending on certain conditions

	 

' taking that data and display it in another sheet 

    Worksheets(Tool\_Chargeback\_Calculation\_TAB).Range(Chargeback\_Calculation\_First\_Task).Offset(i - 1, 0).Value = TEMP\_Unit\_Task	' (1) 

' take that same data and sum it in a total to be displayed outside the loop 

' the only place in the whole procedure where this variable is used (other then the =0 before the loop) 

    Total\_Client\_Unit\_Task = Total\_Client\_Unit\_Task + TEMP\_Unit\_Task 

. . . 

	 

next i

    Worksheets(Tool\_Chargeback\_Calculation\_TAB).Range(Chargeback\_Calculation\_First\_Base).Offset(HowManyExtractedClients, 0).Value = Total\_Client\_Unit\_Base	'(2) 

Tool_Creation_Step6_TAB sheet: sheet where user manually entered the data

31.00000000

240.00000000

336.00000000

204.00000000

233.00000000

662.00000000

4276.00000000

636.00000000

180.00000000

1.00000000

0.13330000

3.00000000

745.00000000

58.00000000

173.00000000

387.00000000

326.00000000

91.00000000

0.00000000

597.00000000

3166.00000000

687.00000000

360.00000000

Tool_Chargeback_Calculation_TAB sheet: sheet where the data is re-displayed with other informations (not displayed here) and at the end we display the total we cumulated inside the tool for those same values. Using format number 8 digits everywhere

31.00000000 (1)

240.00000000 (1)

336.00000000 (1)

204.00000000 (1)

233.00000000 (1)

662.00000000 (1)

4276.00000000 (1)

636.00000000 (1)

180.00000000 (1)

1.00000000 (1)

0.13330001 (1) It now has an extra digit???

3.00000000 (1)

745.00000000 (1)

58.00000000 (1)

173.00000000 (1)

387.00000000 (1)

326.00000000 (1)

91.00000000 (1)

0.00000000 (1)

597.00000000 (1)

3166.00000000 (1)

687.00000000 (1)

360.00000000 (1)

13392.13281250 (2) Why did it loose some digits in the loop?

13392.13330001 I put in a cell the SUM() command and used all the printed data (without the total), it shows there is a difference with the VBA Total calculated

Answer 1

Why did it loose some digits in the loop?

The data type of the values in a sheet is Double, use that instead of Single.

Because of the digits... there is an answer, but it's really hard to understand. It's the question how machines process decimal numbers.

Numeric precision in Microsoft Excel - Wikipedia

IEEE 754 - Wikipedia

See also
Floating-point arithmetic may give inaccurate results in Excel

Andreas.

Answer 2

Thank you

It works now.,

Good to know about this.

Answer 3

Excel stores numbers in cells as Double data type so you should use that.