Wrong calculation in Excel 2000

[bkast3again]

I have an Excel worksheet that has several cells that do a simple calculation to multiply a fee charged to several hrs of work. However the end calculation in off by .30 in either direction. The calculation looks like this. the first cell calculates over 31 cells (days in a month) and then divides by 60 to calculate to an hour fractions and then multiplies it by the hourly rate.

Cell one = =PRODUCT(A1,A31)/60
Cell two = 91(this is the hourly rate)
Cell three = =A32*A33

where A32 is cell one above and A33 is cell two above. And through out the worksheet the end number is off by the .30 . Sometimes it is exactly correct. Driving my billing dept. crazy.

 

[SkipVought]

please submit the exact values of each value in the calculation in question and your expected result value.

BTW, =PRODUCT(A1,A31) calculates the product of TWO values; the value in A1 and the value in A31. Does not matter what values are in between.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bkast3again]

I dont have the file in with me. It is at work. You are right the calculation is the sum of whatever is between 1 thru 31. I can repost tomorrow I just thought perhaps excel has a known problem. The end result truly is sometimes right and sometimes up by .30 or down by .30. I even switched the values from a line that was right to a wrong one and they remained wrong. I looked at the decimal values and cant figure it out. It looks like if the end value of 1 through 31 ends in a 0 or 5 it comes out correct. Again I can repost tomorrow.

 

[SkipVought]

You are right the calculation is the sum of whatever is between 1 thru 31.

But you are NOT doing that with the formula you posted!

What you may want is
[tt]
=SUM(A1:A31)/60
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bkast3again]

You are correct and that is what the calculation is.
When I send the bill it sort of balances out between the ups and downs and is only off a few cents either direction but the agency I am billing keeps pointing out the minor calculations. I can avoid it by sending a different bill due to me knowing what is going on but there has to be a reason why it does this. Thanks for looking. Should I repost in a new post or just add to this thread and you can look at it? or I can attach the file but I have to take out a bunch of names. Either way I will do it. Also I may check it out in excel 2003 and see if the problem goes away. Thanks again.

 

[SkipVought]

Only you can determine if the calculation does what is required.

Your original formula returned the quotient of the product of two values and 60.

Your corrected formula would return the quotient of the sum a range of 31 values and 60.

You can continue to post in this thread. No need to attach a file at this point.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[1726]

Division by 60 may be creating rounding errors.

Formatting the result displayed in cell one to a set number of decimal places does not change the underlying value.

Your formula in cell three sees the underlying value not the one displayed in the cell.

To ensure that the underlying value is the same as that displayed you need to use the Round function

e.g. =ROUND(SUM(A1:A31)/60,x)

where x is the number of decimal places used by the cell format.