Excel Calculation Error - how to use ROUND function

Question

I have a very simple spreadsheet with a column of 3 numbers each of which are calculated from a 2 number ratio. An average value is computed for the 3 calculated numbers and this average multiplied by a fixed number. The answer is incorrect by 10.

first number = 83758/9484 = 8.83

second number = 107500/10132 = 10.61

third number = 103700/11120 = 9.33

Average calculated by average function = 9.59

Multiplying the average 9.59 by 9520 returns the value 91287

The correct answer is 91297   Why?

Roger

Answer 1

Hi Roger,

What you see is not always the same as what is there.

I applied a numberformat with 2 decimals in the second column, the third column shows the value with all decimals.

= 83758/9484	8.83	8.83150569380008
= 107500/10132	10.61	10.60994867745760
= 103700/11120	9.33	9.32553956834532
Average	9.59	9.58899797986765
Mult	91287.26	91287.26076834010000

Excel is using the real value iso of the displayed value, except if you set "precision as displayed" what I  do not recommend.

As you can see, the difference between the displayed average value and the real value is 0.00100202013234529

Multiply this value with 9520 gives: 9.5392316599272 what is exactly the difference between 91287 & 91297.

Personally I use always the real values and use the ROUND function if needed. It is up to the user to decide, case by case, what is the best approach.

Although Excel displays only 15 meaningful digits the iternal value has much more digits.

The real value of =83758/9484 is 8.83150569380008**,** 43525938422606495149725854

The part before the comma shows the value that Excel can display but the used value is the one with all digits.

Due to the limits of the floating point conversion it is not possible to convert this value to a binary equivalent.

The real internally used value is: 8.83150569380008**,** 39892310978029854595661163330078125

As you can see there is a small difference.

0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
-1.11022E-16

IN A1:A10 I entered 0.1, in A11:=SUM(A1:A10)-1-0

1-1-0 should be 0 but Excel gives a very small value. Why?

Because 0.1 has no exact binary equivalent and 0.100000000000000**,**0055511151231257827021181583404541015625

is really used.

Answer 2

Hi Rasoul,

See the explanation of Joeu2004 in following thread: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/miscalculations-in-ranking/5ab403bb-6da0-41d1-a89c-02ce98b83ec1?page=~pagenum~

Answer 3

0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
-1.11022E-16

IN A1:A10 I entered 0.1, in A11:=SUM(A1:A10)-1-0

1-1-0 should be 0 but Excel gives a very small value. Why?

Because 0.1 has no exact binary equivalent and 0.100000000000000**,**0055511151231257827021181583404541015625

is really used.

Dear Ronse

What is the meaning "-0" in A11:=SUM(A1:A10)-1-0?

Because if you put A11:=SUM(A1:A10)-1, it will give 0 which is correct.

Answer 4

Hi,

In your calculation the result of the average is rounded to 2 places after the decimal. Excel is using numbers with more digits in the decimal places to calculate. The difference is due to this. If you'd want to use rounded numbers in your calculation, see the ROUND function: https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c

Regards.