display correct numeric value for more than 15 digits

[VDS]

Dear All,

If we add any numeric value in a particular cell with more than 15 digits, the last digit will automatically converted to zero. In such a case,any addition, substraction will also result in Zero. I want to do the calculations. After checking, it is found that it is shown in numeric value only.

Attach herewith sample data with fomula. How it can be done.

Help is requested.


VDS

 

[chirayu]

https://support.microsoft.com/en-ph...to-zeroes-when-you-type-long-numbers-in-cells
http://access-excel.tips/excel-maximum-number-of-digits-workaround/

 

[chirayu]

to be honest excel can't handle numbers more than 15 digits long for calculations

 

[bosco_yip]

1] Excel store number cannot excess 15 digits and excess 15 digits number cannot do further addition or subtraction calculation

2] The workaround in your example 16 digits number is done by break it to 1+15 digits and something like this. The result become text.

3] In E9, copied down :

=IF(LEN(E$8)>15,SUBSTITUTE(E$8,RIGHT(E$8,15),RIGHT(E$8,15)+ROW(A1)),E$8+ROW(A1))

Regards
Bosco

 

[GraH - Guido]

Some more on the topic might interest you. I've posted a link to add-ons claiming to deal with this. Never tried them, since I am not working with such numbers.
https://chandoo.org/forum/threads/largest-number.37272/