-1

I have an Excel sheet where i am multiplying 4 cell, with one cell containing a formula. But the result is wrong.

Screen shot of the excel calculation

The formula is A1*B1*D1*E1. In the first row the cell D1 value has been entered manually. D1 is actually C1/1000.

In the 6th row the the cell D6 is calculated using =C6/1000. But as you can see the results F1 AND F6 are different. F1 gives 100.67925. and F6 gives 100.41255. The correct answer is actually F1 =100.67925.

All the cells are in number format and no rounding has been done.I dont know what i am doing wrong. Please give a solution to my problem. Thank you.

SOLVED

I found out the problem. As Máté Juhász and elserra pointed out it was due to rounding problem. The rounding problem occurred because i changed the number format from General to Number. I again went and typed 1.51 in to D1 and hence the result in F1 was obtained. I didnt check the actual value for D1 in the formula bar. i was checking rounding by changing the decimal value places. Thank you for your solution.

4
  • 6
    "no rounding has been done" ?? C6 is 1506, 1506/1000=1.506 and not 1.51 as in your screenshot. If you use 1.506 then you get the same result as Excel. Feb 2, 2019 at 15:49
  • Thank you for your reply. :) Yes you are correct 1506/1000=1.506. I have pasted the wrong screenshot sorry. But the values displayed in D1 and D6 are same. So in that case the results should be same right?. Both D1 and D6 are displaying the same decimal places. I tried again with D6 =(C3/1000), D6 is calculated as 1.506 as you said but F6 is shown as 100.4126. when D6 is directly given as 1.506 then the correct result is given. What am i doing wrong??
    – princeton
    Feb 8, 2019 at 6:43
  • Please post the right screenshot then. Feb 8, 2019 at 7:09
  • Hey i checked once again. Seem like i posted the right one. I found out why that was happening. It was my mistake only. You were right it was due to rounding. But the rounding was not because of the decimal places. it was because of the number format. i entered all the data in general format then changed them to number format. then i again went to D1 and typed 1.51 manually when cross checking with the original excel sheet. that's how i got the data in the screenshot. Thank you for your help.
    – princeton
    Feb 8, 2019 at 9:09

1 Answer 1

0

When having troubles like this, first try with easy numbers so you can follow the steps, for example 2 , 4 , 10... If you grab a calculator, you will see that 3 x 2.5 x 1506 / 1000 x 8.89 = 100.41255. If you do the same with 3 x 2.5 x 1.51 x 8.89 you will get 100.67925.

This means you're doing it right and Excel is also calculating everything correct. Look if the source telling you that 100.41255 is wrong may be incorrect. As @Mate points out, this source may be rounding the value for D1.

1
  • Hi thank you for your response. i found out why that was happening. Both of you were correct it was due to rounding off. but that rounding off was taking place when changing the number format from general to Number. I didnt pay attention the number displayed in the formula bar, and again entered 1.51 in D1, That was how i got the result in the screenshot. thank you
    – princeton
    Feb 8, 2019 at 9:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .