excelexcel-formulaexcel-2010

why there is a difference between excel multiplication with formula and raw value multiplication


This is my Excel data:

enter image description here

In cell I21, I am using multiplication formula like Round(G21 * H21, 0) and the result is 94185.

But the cell J21 contains raw multiplication like Round(17.18 * 5480.96, 0) and the result is 94163.

Help me to understand the difference. I need the actual result to be 94163.


Solution

  • What are your actual values - not your displayed values?

    Cell G21 displays the value 17.18; this has, however, clearly been set to show 2 decimal places (rounded), since there are items in the column that end in a decimal zero.

    Similarly, Cell H21 displays the value 5480.96

    Now, 5480.96 * 17.18 = 94162.86, which rounds to 94163; but 548.96 * 17.184 = 94184.82, which rounds to 94185; and 17.184 displayed to two-decimal-places will show as 17.18 (because 4 is less than 5)

    So, you need to decide: do you want to Round to two decimal places at the interim step (=ROUND(ROUND(G21, 2) * ROUND(H21, 2), 0)); or do you want to continue doing the full calculation and only rounding the final result?

    The difference is important — as can be seen in this question on Skeptics.SE. The computer is currently doing exactly what you asked, and displaying the correct value for what you told it to calculate: but, did you ask it to calculate the right thing?

    (With an extra decimal place involved, this is similar to how Edward Lorenz' work on Weather / Meteorology lead to pioneering work on Chaos Theory)