Why does Excel round the least significant digit when I paste in a large number?
up vote
2
down vote
favorite
If I paste the number 1542819813881128.5 into a cell of Number format in Excel the pasted value is 1542819813881120.00.
Why do these applications round down the 8.5? Is there a way of preventing this?
microsoft-excel
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
asked Nov 21 at 17:17
MM01
11815
add a comment |
up vote
2
down vote
favorite
If I paste the number 1542819813881128.5 into a cell of Number format in Excel the pasted value is 1542819813881120.00.
Why do these applications round down the 8.5? Is there a way of preventing this?
microsoft-excel
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
asked Nov 21 at 17:17
MM01
11815
You can only configure Excel to ROUNDUP or ROUNDDOWN, Excel only supports 15 significant digits, so a number in Excel will be rounded in one direction no matter what.
– Ramhound
Nov 21 at 17:44
See also Adding more than 15 digits in Excel. One of the answers suggest to use an addin for large number maths.
– Doc Brown
Nov 22 at 7:08
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
If I paste the number 1542819813881128.5 into a cell of Number format in Excel the pasted value is 1542819813881120.00.
Why do these applications round down the 8.5? Is there a way of preventing this?
microsoft-excel
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
asked Nov 21 at 17:17
MM01
11815
If I paste the number 1542819813881128.5 into a cell of Number format in Excel the pasted value is 1542819813881120.00.
Why do these applications round down the 8.5? Is there a way of preventing this?
microsoft-excel
microsoft-excel
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
asked Nov 21 at 17:17
MM01
11815
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
asked Nov 21 at 17:17
MM01
11815
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
edited Nov 21 at 17:39
Twisty Impersonator
17.3k126293
17.3k126293
asked Nov 21 at 17:17
MM01
11815
asked Nov 21 at 17:17
MM01
11815
asked Nov 21 at 17:17
MM01
11815
11815
You can only configure Excel to ROUNDUP or ROUNDDOWN, Excel only supports 15 significant digits, so a number in Excel will be rounded in one direction no matter what.
– Ramhound
Nov 21 at 17:44
See also Adding more than 15 digits in Excel. One of the answers suggest to use an addin for large number maths.
– Doc Brown
Nov 22 at 7:08
add a comment |
You can only configure Excel to ROUNDUP or ROUNDDOWN, Excel only supports 15 significant digits, so a number in Excel will be rounded in one direction no matter what.
– Ramhound
Nov 21 at 17:44
See also Adding more than 15 digits in Excel. One of the answers suggest to use an addin for large number maths.
– Doc Brown
Nov 22 at 7:08
You can only configure Excel to ROUNDUP or ROUNDDOWN, Excel only supports 15 significant digits, so a number in Excel will be rounded in one direction no matter what.
– Ramhound
Nov 21 at 17:44
You can only configure Excel to ROUNDUP or ROUNDDOWN, Excel only supports 15 significant digits, so a number in Excel will be rounded in one direction no matter what.
– Ramhound
Nov 21 at 17:44
See also Adding more than 15 digits in Excel. One of the answers suggest to use an addin for large number maths.
– Doc Brown
Nov 22 at 7:08
See also Adding more than 15 digits in Excel. One of the answers suggest to use an addin for large number maths.
– Doc Brown
Nov 22 at 7:08
add a comment |
1 Answer
1
active
oldest
votes
up vote
3
down vote
accepted
Spreadsheets are not designed for highly accurate calculations. The IEEE 754 specification for floating point math calls for only the 15 most significant digits to be stored. If the integer portion of a number is 15 places or longer, the mantissa or fractional part will be dropped.
The solution is to perform your calculations in something other than a spreadsheet, which is vexing for us who have gotten really good at spreadsheets.
answered Nov 21 at 17:33
K7AAY
3,22221437
1
Mind blown. This is an antiquated constraint.
– MM01
Nov 21 at 17:51
softwarerecs.stackexchange.com/questions/53292/…
– K7AAY
Nov 21 at 18:03
1
@MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense.
– Aganju
Nov 21 at 20:10
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
Spreadsheets are not designed for highly accurate calculations. The IEEE 754 specification for floating point math calls for only the 15 most significant digits to be stored. If the integer portion of a number is 15 places or longer, the mantissa or fractional part will be dropped.
The solution is to perform your calculations in something other than a spreadsheet, which is vexing for us who have gotten really good at spreadsheets.
answered Nov 21 at 17:33
K7AAY
3,22221437
1
Mind blown. This is an antiquated constraint.
– MM01
Nov 21 at 17:51
softwarerecs.stackexchange.com/questions/53292/…
– K7AAY
Nov 21 at 18:03
1
@MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense.
– Aganju
Nov 21 at 20:10
add a comment |
up vote
3
down vote
accepted
Spreadsheets are not designed for highly accurate calculations. The IEEE 754 specification for floating point math calls for only the 15 most significant digits to be stored. If the integer portion of a number is 15 places or longer, the mantissa or fractional part will be dropped.
The solution is to perform your calculations in something other than a spreadsheet, which is vexing for us who have gotten really good at spreadsheets.
answered Nov 21 at 17:33
K7AAY
3,22221437
1
Mind blown. This is an antiquated constraint.
– MM01
Nov 21 at 17:51
softwarerecs.stackexchange.com/questions/53292/…
– K7AAY
Nov 21 at 18:03
1
@MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense.
– Aganju
Nov 21 at 20:10
add a comment |
up vote
3
down vote
accepted
up vote
3
down vote
accepted
Spreadsheets are not designed for highly accurate calculations. The IEEE 754 specification for floating point math calls for only the 15 most significant digits to be stored. If the integer portion of a number is 15 places or longer, the mantissa or fractional part will be dropped.
The solution is to perform your calculations in something other than a spreadsheet, which is vexing for us who have gotten really good at spreadsheets.
answered Nov 21 at 17:33
K7AAY
3,22221437
Spreadsheets are not designed for highly accurate calculations. The IEEE 754 specification for floating point math calls for only the 15 most significant digits to be stored. If the integer portion of a number is 15 places or longer, the mantissa or fractional part will be dropped.
The solution is to perform your calculations in something other than a spreadsheet, which is vexing for us who have gotten really good at spreadsheets.
answered Nov 21 at 17:33
K7AAY
3,22221437
edited Nov 21 at 17:39
answered Nov 21 at 17:33
K7AAY
3,22221437
answered Nov 21 at 17:33
K7AAY
3,22221437
answered Nov 21 at 17:33
K7AAY
3,22221437
3,22221437
1
Mind blown. This is an antiquated constraint.
– MM01
Nov 21 at 17:51
softwarerecs.stackexchange.com/questions/53292/…
– K7AAY
Nov 21 at 18:03
1
@MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense.
– Aganju
Nov 21 at 20:10
add a comment |
1
Mind blown. This is an antiquated constraint.
– MM01
Nov 21 at 17:51
softwarerecs.stackexchange.com/questions/53292/…
– K7AAY
Nov 21 at 18:03
1
@MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense.
– Aganju
Nov 21 at 20:10
1
1
Mind blown. This is an antiquated constraint.
– MM01
Nov 21 at 17:51
Mind blown. This is an antiquated constraint.
– MM01
Nov 21 at 17:51
softwarerecs.stackexchange.com/questions/53292/…
– K7AAY
Nov 21 at 18:03
softwarerecs.stackexchange.com/questions/53292/…
– K7AAY
Nov 21 at 18:03
1
1
@MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense.
– Aganju
Nov 21 at 20:10
@MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense.
– Aganju
Nov 21 at 20:10
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1377362%2fwhy-does-excel-round-the-least-significant-digit-when-i-paste-in-a-large-number%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
You can only configure Excel to ROUNDUP or ROUNDDOWN, Excel only supports 15 significant digits, so a number in Excel will be rounded in one direction no matter what.
– Ramhound
Nov 21 at 17:44
See also Adding more than 15 digits in Excel. One of the answers suggest to use an addin for large number maths.
– Doc Brown
Nov 22 at 7:08