Richard Detwiler wrote: > Bob Long wrote: >> "Matt Wlazlo" <[EMAIL PROTECTED]> wrote in message >> news:[EMAIL PROTECTED] >> >>> On 6/3/06, Bob Long <[EMAIL PROTECTED]> wrote: >>> >> >> >>>>>>> I'm noticing some strange behavior while calculating prices on my >>>>>>> invoice. >>>>>>> >>>>>>> A1=39:50 (hours worked) >>>>>>> A2=A1*24 (==39.83) >>>>>>> A3=65 (rate) >>>>>>> A4=A3*A2 >>>>>>> >>>>>>> Now A4 will equal 2589.17. But, somewhere else in the spreadsheet, >>>>>>> typing =39.83*65 gives 2588.95! >>>>>>> >> >> >>>>>> I think you'll find it is a rounding issue. If you change the format >>>>>> of A2 >>>>>> so that you see more decimals, you'll see that it is really 39.833333 >>>>>> (recurring, I presume). Multiply *that* by 65 and you'll get 2589.17. >>>>>> >>>>>> >>>>> I tried re-adjusting the formatting of A2, but to no avail. >>>>> >>>> More specifically, what did you do? Did you change it so you see more >>>> decimal points? You should be able to see 39.833333... rather than just >>>> 39.830000. >>>> >>>> >>> I changed the number of decimal points. The cell reads 39.833333333... >>> >>> >>>>> Strangely, if I manually calculate =39.83*65, it correctly gives >>>>> 3588.95. Even windows calculator gives the same result. So I don't >>>>> think it's a fp precision problem. >>>>> >>>> It's not a precision thing. Calc is holding the correct value internally. >>>> It's just that 39.833333... is not the same number as 38.830000! >>>> >>> Ahh! Finally got it. I used ROUND(A2;2)*65 to get the correct answer. >>> >> >> Which just begs the question of what is *really* correct! 39:50 (hh:mm) is >> 39.833333... (recurring). 39:50 is *not* 39.8300. So if you are rounding to >> lose some digits, you *may* be propogating errors in subsequent >> calculations. It really depends on how you are defining things for your >> purpose; just be careful, is all I'm saying. >> >> > > I agree with Bob. Rounding does NOT give you the "correct" answer. The > correct answer is the one you had in the first place, even though it > didn't seem correct on the surface, because if you used 38.8300000 in > the calculation, you'd get a different answer. But the one you got in > the first place was "correct", and the one you got when you used > 38.8300000 was "incorrect". So I'm not sure why you're introducing the > ROUND function to knowingly give you an incorrect answer. > > In any case, the difference between rounded and not rounded is about > 0.008% -- in my opinion hardly enough to worry about. But you did have a > concern that you had this tiny difference, in which case, I'm again not > sure why you would ROUND to give you an answer which is incorrect.
While rounding perhaps does not give the correct answer mathematically, I think it important to keep in mind that the spreadsheet is an invoice based on hours:min and currency. Reverting to simple math: Matt charges $65.00 per hour = $1.083333 per minute Matt worked 39 hours and 50 minutes = 2390 minutes 1.083333*2390=$2589.17 (actually $2,589.166666667) The problem is that nobody charges $1.083333 per minute (except perhaps a government or a US gas/petrol station) and nobody submits their charge/work time as 39.83333 hours. So Matt will need to either Round, Truncate, or Fixed his results or variables. If charging by the minute is important he can adjust his rates to $1.08 per minute ($64.80 per hour actual) or $1.09 per minute ($65.40 per hour actual). Or, simply state that all minutes are rounded up (or down if he's feeling generous) to the nearest minute. If he uses =FIXED(A1)*24 or =ROUND(A1;2)*24 in A2 the result is 39.84 and like most financial spreadsheets & governments rounds up. IMHO that is the easiest way to go... not to mention that it results in $2589.60 for his time. BTW & FWIW for the OP: MS Excel provides the exact same responses/results, so OOoCalc doesn't have a bug/problem that I can see. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
