Excel Multiplication error when multiplying two cells where one cell has a calculated value
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have an Excel sheet where i am multiplying 4 cell, with one cell containing a formula. But the result is wrong.
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.
microsoft-excel microsoft-excel-2007
asked Feb 2 at 15:38
princetonprinceton
22
add a comment |
I have an Excel sheet where i am multiplying 4 cell, with one cell containing a formula. But the result is wrong.
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.
microsoft-excel microsoft-excel-2007
asked Feb 2 at 15:38
princetonprinceton
22
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.
– Máté Juhász
Feb 2 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 at 6:43
Please post the right screenshot then.
– Máté Juhász
Feb 8 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 at 9:09
add a comment |
I have an Excel sheet where i am multiplying 4 cell, with one cell containing a formula. But the result is wrong.
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.
microsoft-excel microsoft-excel-2007
asked Feb 2 at 15:38
princetonprinceton
22
I have an Excel sheet where i am multiplying 4 cell, with one cell containing a formula. But the result is wrong.
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.
microsoft-excel microsoft-excel-2007
microsoft-excel microsoft-excel-2007
asked Feb 2 at 15:38
princetonprinceton
22
asked Feb 2 at 15:38
princetonprinceton
22
edited Feb 8 at 9:57
princeton
asked Feb 2 at 15:38
princetonprinceton
22
asked Feb 2 at 15:38
princetonprinceton
22
asked Feb 2 at 15:38
princetonprinceton
22
22
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.
– Máté Juhász
Feb 2 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 at 6:43
Please post the right screenshot then.
– Máté Juhász
Feb 8 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 at 9:09
add a comment |
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.
– Máté Juhász
Feb 2 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 at 6:43
Please post the right screenshot then.
– Máté Juhász
Feb 8 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 at 9:09
6
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.
– Máté Juhász
Feb 2 at 15:49
"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.
– Máté Juhász
Feb 2 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 at 6:43
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 at 6:43
Please post the right screenshot then.
– Máté Juhász
Feb 8 at 7:09
Please post the right screenshot then.
– Máté Juhász
Feb 8 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 at 9: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 at 9:09
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Feb 5 at 14:01
elserraelserra
2614
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 at 9:15
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1401341%2fexcel-multiplication-error-when-multiplying-two-cells-where-one-cell-has-a-calcu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Feb 5 at 14:01
elserraelserra
2614
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 at 9:15
add a comment |
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.
answered Feb 5 at 14:01
elserraelserra
2614
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 at 9:15
add a comment |
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.
answered Feb 5 at 14:01
elserraelserra
2614
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.
answered Feb 5 at 14:01
elserraelserra
2614
answered Feb 5 at 14:01
elserraelserra
2614
answered Feb 5 at 14:01
elserraelserra
2614
answered Feb 5 at 14:01
elserraelserra
2614
2614
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 at 9:15
add a comment |
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 at 9:15
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 at 9:15
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 at 9:15
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1401341%2fexcel-multiplication-error-when-multiplying-two-cells-where-one-cell-has-a-calcu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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.
– Máté Juhász
Feb 2 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 at 6:43
Please post the right screenshot then.
– Máté Juhász
Feb 8 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 at 9:09