hours and $$ not calculating correctly

[smada lerual]

I feel a little crazy but when I put together the spreadsheet for time and a 1/2 I thought it was working brilliantly until..... someone questioned the math, so I got my calculator out and sure enough the calculations don't match and even more importantly our payroll people said it was wrong too! I don't mind uploading something to look at but I must warn you that I am the worst kind of excel user. I am self taught so I have bad habits.

Here is what is going on... 8.42 hours formatted as a number with 2 decimal places * $23.25 formatted as currency = $195.69
my calculator says that it should equal 195.765 which I would round up to 195.77 (if that mattered)
another one... 8.78*23.25 (formatted the same as the previous) =$204.21
calculator says that it should equal 204.135
3.63 hour* 23.03 =$83.66
calculator says....83.598

help???
thanks for your thoughts in advance- Laurel

 

[Eric W]

I'm sure it's rounding, based on the number of hours. Consider:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	8.42	23.25	195.765		8.42	23.25	195.69
2	8.78	23.25	204.135		8.78	23.25	204.21
3	3.63	23.03	83.5989		3.63	23.03	83.66
4
5
6				TRUE	8.41677	23.25	195.69		195.765
7				TRUE	8.78323	23.25	204.21		204.135
8				TRUE	3.63265	23.03	83.66		83.5989
Sheet2

Cell Formulas
Range	Formula
C1:C3,G6:G8,G1:G3	C1	=A1*B1
D6:D8	D6	=E1=E6
I6:I8	I6	=ROUND(E6,2)*F6

The A1:C3 range has the formulas that you quoted in your post, and I get the same answer as the calculator. I used the values that you quoted.

Now the E1:G3 range also has the same numbers you quoted in your post, BUT now I get the values that are off by a little. What's the difference? The values in E1:E3 are not "exact". The "exact" values I have in E6:E8. The formulas in D6:D8 show that they are the same. The only difference is that the ones in E1:E3 only display 2 decimals, but the E6:E8 values show 5 decimals. No matter how many decimals you show, Excel will calculate using all the decimals in the number. If you want to use the rounded value in your calculation, you have to explicitly tell it to, see the formulas in I6:I8, which match your values from C1:C3.

I assume your hour values are calculated somehow. Since Excel keeps times as fractions of a day, they can easily turn to some strange decimal value.

There is one exception to this. There's a setting in Excel called "precision as displayed", which you can set which will give you the values without using ROUND in the formula, but this is not recommended.

Hope this helps!