I have a spreadsheet, again, that has an amount error after the calculations. I tried to attach it but I hit browse my computer highlight the file add attachment and nothing. Tell me how to attach and I will.



Thanks in advance

You need to click the "Go Advanced" button below the Reply box... then click the paperclip icon to browse and upload your file.

Very likely, this is due to a common issue with binary computer arithmetic, due to the fact that most decimal fractions cannot be represented exactly.

For example, IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!) because 10.01 - 10 is 0.00999999999999979.

In general, if you expect arithmetic to be accurate to n decimal places, you should explicitly round the expression to that number of decimal places.

(Not to an arbitrary number of decimal places like 10, as some people suggest.)

For example, IF(ROUND(10.01 - 10, 2) = 0.01, TRUE) returns TRUE, as expected.


See the paperclip icon in the toolbar for the Message pane. If that does not work, upload the Excel file to a file-sharing website, and post the public/share URL in a message here.

I do the go advanced, hit the paper clip, hit browse, click on my file, hit open, and nothing.

JOEu2004, I understand what you are saying, but cannot fix it. All my decimals are down to 2 places. I do however have a convoluted formula and it is to low by 50 plus cents on some og the answers. I wish I could post this here.. but it wont let me attach. It's only like 19 kb in size.

If by "down to 2 places", you mean that you are formatting to display 2 decimal places, that is not sufficient.

Formatting alone only affects how a value appears. It does not affect the actual cell value.

Moreover, as my example with 10.01 - 10 demonstrates, constants might have only 2 decimal places, but calculations with those constants might result in values with more decimal places because of the binary arithmetic anomalies that I alluded to before.

Finally, if results are significantly off (by 50+ cents, for example), the mistake could be in your calculations. Not really a mistake per se; but again, failing to round calculations based on your expectations.

For example, =12.45*10% in A1 is 1.245. That might display 1.25 if you format to 2 decimal places. So you might expect a subsequent calculation like =A1*10 to be 12.50, but in fact it is 12.45. And if you sum a bunch of calculations like my example in A1, the cumulative effect can be a significant "error" -- that is, a deviation from your expectations based on displayed values (appearances).

For all of these examples, the remedy is the same: explicitly round to the precision that you expect calculations to be accurate to. That means: use the ROUND function.

Did you even try it?


As I said, upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website, and post the public/share URL here.

One such website is box.net/files. After uploading the file, be sure to click on Share to get the public URL.

PS.... Arguably, that is not always easy to do. For example, if you use pivot tables, calculations are not rounded by default. I'm not a PT person. But I believe the only work-around is to create "calculated fields" (?) instead of the normal PT set-up.

-----

Another work-around that I do not usually recommend is: set the "Precision As Displayed" Advanced Option.

If you want to experiment with that, be sure to make a backup copy of the Excel file first.

PAD is very dangerous; setting it can cause immediate and irreversible changes to constants throughout the Excel file.

Moreover, setting PAD does not fix all rounding problems. It does not fix my earlier example, for instance: IF(10.01 - 10 = 0.01, TRUE).

But just trying it, then throwing away the Excel file with PAD set, might give you confidence in the need to explicitly round formulas and expressions selectively.

if I have a formula already in the cell how do ZI add a second formula such as =round. Please keep in mind I am a novice user and still learning. I do appreciate your help and am doing my best to understand. my file should be available soon

the 2 numbers in red are the different ones. The red number to the right of net check amount is correct the red number below gross wages is incorrect.

PS in another cell I did the round function and have my difference down to 3 cents

Thanks again for your help

I looked at the file. What I see is....

Both I2 ("gross wage") and L1 ("check amount") appear to be the same, namely $368.46.

Their actual values differ from the displayed values. In particular, I2 is $368.4575, and L1 is $368.458333333333.

That difference can and should be fixed with explicit rounding. The formulas should be:

I2: =ROUND(((H2-40)*9.25*1.5)+(40*9.25)+0.77, 2)
L1: =ROUND(H2*9.25, 2)

where H2 is $39.8333333333333.

However, it is unclear why the displayed values should be the same. And perhaps that is your point, to some degree.

In fact, we can demonstrate algebraically that I2-L1 should be (H2-40)*9.25*0.5 + 0.77. And they do indeed differ by about that much.

But more to the point: I believe that both formulas are incorrect, if your intent is to incorporate 1.5x overtime pay into gross wages in I2.

(Certainly, the formula in L1 does not do that. That formula seems irrelevant.)

I am only familiar with Calif overtime rules. But I suspect they are similar in most states. Based on Calif rules, the calculation in I2 should be:
where G2:G8 are the daily hours in a work week, 9.25 is the hourly rate, and 1.5 is the overtime factor.

SUMPRODUCT((G2:G8>8)*(G2:G8-8)) is the sum of the daily overtime hours in excess of 8 hours per day for the week.

SUMPRODUCT((G2:G8<=8)*G2:G8 + (G2:G8>8)*8) is the sum of the daily regular hours for the week, up to 8 hours per day. (The daily excess is already accounted for in the previous paragraph.)

Thus, MAX(0, SUMPRODUCT((G2:G8<=8)*G2:G8 + (G2:G8>8)*8) - 40) is the overtime hours in any excess of 40 regular hours for the week. (Zero if there are fewer than 40 regular hours.)

With that formula, the actual and displayed gross wages in I2 is $376.71.

Note: Alternatively, an employer might choose to round each component calculation separately. In that case, gross wages in I2 would be $376.70.

Does that help? Or did I misunderstand your intentions or how overtime is calculated for your state?

If the latter, please specify your state and the overtime calculations as you understand them.

-----

There are other rounding issues that I believe you should address.

The formulas in I4:I8 should be explicitly rounded. For example, in I4:
A formula in I9 should also be explicitly rounded. But since it is a constant in your example, no additional rounding is required (and it would be redundant).

Without explicit rounding, the actual original values (based on the incorrect overtime calculation in I2) are:

I4: $27.983625
I5: $5.96553045
I6: $22.844365
I7: $5.34263375
I8: $3.684575

On the other hand, I believe it is correct not to explicitly round the decimal hour calculations in G2:G9.

That choice demonstrates why setting the "Precision As Displayed" option would be incorrect, since you choose to display decimal hours with only 2 decimal places.

The problem was resolved in email.

The primary problem was knowing how to calculate overtime for Ohio. It is much simpler than Calif: just 1.5 time for hours worked over 40. So the formula in I2 should be:

=ROUND(H2 + MAX(0,H2-40)*0.5) * 9.25, 2)

There were also some formulas that require explicit rounding. But that issue was secondary and not related to the "difference".

Interestingly, the example Excel file demonstrates why "Precision As Displayed" should not be set in this case.

Work time was calculated for each day of the week and converted to decimal hours. The number of hours needed to be calculated exactly (not rounded), but they are formatted to display only 2 decimal places. Setting PAD would have rounded those values to 2 decimal places.