Rounding Issue in Accounting Workbook

**srose9** · 04-01-2021, 11:23 AM

Hi, everyone! I have a strange issue and am hoping to find some guidance here. I have a workbook with a sheet that contains the total wages and taxes for each employee, as well as a sheet that shows how many hours the employee spent on different tasks. I wrote a macro to calculate a breakdown of the total wages and taxes by task. However, when I add the breakdown back up, the calculated total does not match the total from the wages/taxes sheet. I have included a screenshot that shows the breakdown as two columns, the left value is the wages value and the right value is the tax value and below the blue mark is first the calculated total and below is the true total. Notice that they disagree by 1/1000 of a penny. Not a big deal except I have hundreds of employees and in the end, my calculations are off by $0.20. Is there someway to apply a precision rule so that the user of the macro (I am writing this to make life easier for an accountant so I do not use the macro myself) can sum the column to get the correct total and not the imprecise total? Thank you for any and all advice you can provide! Please let me know if any more information is necessary.

Inkedvba_rounding_error_LI.jpg

The code looks like this:

Please Login or Register  to view this content.

**6StringJazzer** · 04-01-2021, 11:47 AM

You have to round to the appropriate precision at the appropriate point in the calculation. You have just given us a tiny glimpse into the problem. I can't give you a comprehensive solution without seeing all of your calculations, and the results of those calculations on a worksheet.

Offhand I would say you need to do this to force rounding to $0.01.

Please Login or Register  to view this content.

However, if you add these back up and compare the totals to EmployeeWages and EmployeeTaxes you could still get a rounding error.

**srose9** · 04-01-2021, 12:00 PM

Yes, thank you. I did try this and for some reason, the error becomes more egregious in that it becomes 1580.31 calculated vs. 1580.32 truth. This is what caused me to post on the forum because it seemed like a trivial fix but it isn't behaving that way. These are the only calculations I perform, though, so I'm not sure how much else I can offer. Perhaps it helps to know that the hours are logged to the hundredth place (ex: 35.51 hours) and the EmployeeWages and EmployeeTaxes are currency. (I've tried using Value2 to extract their values but this did not help, either.) Anyhow, I've included the full code and a snippet of the Wages and Allocations sheets so you can get a fuller picture of what's going on with my code. Please let me know if that helps at all.

Wages
Attachment 726561

Allocations
Attachment 726562

Please Login or Register  to view this content.

**6StringJazzer** · 07-13-2021, 07:41 PM

I know it's three months later but if you still need help I'll have a look, but your file attachments didn't work.

The paper clip icon does not work for attachments. To attach a file:

Under the text box where you type your reply click the Go Advanced button.
On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
Click the Choose File button to select a file to attached.
Click the Upload button to upload the file.
Then click the Close This Window button. Your file is now attached to your post.