Excel 2007 : Formula result values slightly off

**decadence** · 11-26-2009, 09:21 AM

Hello, I'm having a problem with a very simple multiplication of 2 values in excel which seems to give me a wrong result.I'm sure there is a very simple answer to my problem but i'm just too dizzy atm to find it. I have coloured the problem in the sheet. Thank you in advance.

**DonkeyOte** · 11-26-2009, 09:24 AM

If you wish to round to nearest 10 you will need to use the ROUND function

=ROUND(F33*J36,-1)

(change , to ; pending your locale requirements)

**decadence** · 11-26-2009, 09:44 AM

Rounding is not my problem, the correct calculation for 354,5 x 18,11=6419.995
(thus rounded to 6420) but excel gives me 6421,143951 when it takes the values from the previous formulas.

**DonkeyOte** · 11-26-2009, 09:51 AM

Rounding is most definitely your problem - though I now see that your question is not really rounding to nearest 10 - I did not investigate thoroughly enough based on your file.

To illustrate ....

J36 is not 354,50 it is in fact 354,497 only it is formatted to show as 354,50

Similarly

F33 is not 18,11 it is in fact 18.1133943323727
XL works only to 15 significant digits - thereafter digits revert to 0

By default when conducting calcs XL will use the underlying values rather than the "formatted" number hence the differences
(you can use Precision as Displayed but this is generally speaking ill advised)

So if you want to work to 2 decimal places you must ROUND values accordingly

=ROUND(F33;2)*ROUND(J36;2)

The above would generate 6419.995 which displayed to 0 decimals would of course show as 6420

For more info. on XL Precision etc see: http://support.microsoft.com/kb/78113