Decimal Data type (15 decimal)

%3CLINGO-SUB%20id%3D%22lingo-sub-1531603%22%20slang%3D%22en-US%22%3EDecimal%20Data%20type%20(15%20decimal)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531603%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%20respectable%20members%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20an%20issue%20while%20calculating%20some%20values%20in%20Microsoft%20Excel.%3C%2FP%3E%3CP%3EI've%20an%20equation%20where%20result%20is%20more%20than%2015%20decimals.%20In%20such%20cases%2C%20Excel%20is%20not%20showing%20the%20decimals%20after%2015%20decimals.%20I've%20come%20to%20know%20that%20its%20the%20normal%20behaviour%20of%20Excel.%3C%2FP%3E%3CP%3EHowever%2C%20I%20am%20looking%20that%20what%20logic%20is%20used%20by%20Excel%20(i.e%20Truncate%2C%20Round%2C%20RoundUp%20or%20RoundDown)%20after%20the%2015%20decimals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20better%20explanation%20of%20the%20issue%2C%20I%20am%20posting%20some%20values%20%3A-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EExample%201%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EExcel%20%3D%205.8%2F1200%20%2B%201%20%3D%26nbsp%3B1.00483333333333000000%3C%2FP%3E%3CP%3EScientific%20Calculator%20%3D%205.8%2F1200%20%2B%201%20%3D%26nbsp%3B1.0048333333333333333333333333333%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20above%2C%20it%20seems%20that%20Excel%20is%20Truncating%20the%20values%3C%2FP%3E%3CP%3E%3CSTRONG%3EExample%202%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EExcel%20%3D%20(5.8%2F1200%20%2B%201)%20%5E%20-1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3D%26nbsp%3B0.9951899154088570%3C%2FP%3E%3CP%3ECalculator%20%3D%20(5.8%2F1200%20%2B%201)%20%5E%20-1%20%3D%26nbsp%3B0.99518991540885719024713882899323%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EIn%20above%20it%20seems%20that%20excel%20is%20Truncating%20values%20after%2015%20decimals.%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20the%20difference%20is%20also%20due%20to%20the%20difference%20result%20of%20(5.8%2F1200%2B1)%20%5E%20-1%20in%20Excel%20and%20calculator.%3C%2FP%3E%3CP%3E%3CSTRONG%3EExample%203%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EExcel%20%3D%20(5.8%2F1200%20%2B%201)%20%5E%20-2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3D%200.9904029677314890%3C%2FP%3E%3CP%3ECalculator%20%3D%20(5.8%2F1200%20%2B%201)%20%5E%20-2%20%3D%200.99040296773148832998554204245467%3C%2FP%3E%3CP%3EIn%20above%2C%20it%20seems%20that%20excel%20is%20Rounding%20Up%2C%20instead%20of%20Truncating.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EBottom%20Line%3A-%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20am%20looking%20forward%20a%20logic%20which%20I%20can%20use%20in%20Calculator%20or%20in%20My%20code%2C%20which%20helps%20to%20give%20results%20identical%20to%20Excel.%3C%2FP%3E%3CP%3Ei.e.%3C%2FP%3E%3CP%3EStep%201%20%3A%20First%20changing%20the%20values%20of%205.8%2F1200%20%2B%201%2C%20which%20gives%20result%20identical%20to%20the%20Excel%3C%2FP%3E%3CP%3EStep%202%20%3A%20Then%20changing%20the%20values%20of%20(5.8%2F1200%20%2B%201)%20%5E%20any%20number%2C%20gives%20identical%20result%20to%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EThanks%20in%20advance.%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1531603%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1534670%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Data%20type%20(15%20decimal)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1534670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732989%22%20target%3D%22_blank%22%3E%40Shahid81%3C%2FA%3E%26nbsp%3B%20wrote%3A%20%60%60I%20am%20looking%20forward%20a%20logic%20which%20I%20can%20use%20in%20Calculator%20or%20in%20My%20code%2C%20which%20helps%20to%20give%20results%20identical%20to%20Excel%60%60%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThis%20is%20an%20__Excel__%20forum.%20If%20you%20want%20help%20with%20a%20particular%20calculator%20(app%3F)%2C%20find%20a%20forum%20that%20discusses%20it.%20You%20do%20not%20identify%20the%20calculator.%20You%20refer%20to%20it%20only%20as%20%22Scientific%20Calculator%22.%20If%20you%20are%20referring%20to%20Windows%20Calculator%2C%20it%20might%20be%20helpful%20to%20know%20your%20version%20of%20Windows.%20(I%20don't%20see%20it%20in%20your%20posting%2C%20off-hand.%20But%20I%20might%20have%20overlooked%20it.)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%20any%20case%2C%20what%20you%20are%20asking%20is%20equivalent%20to%20%22how%20can%20I%20use%20a%204-door%20sedan%20as%20if%20it%20has%202%20doors%3F%22.%20We%20cannot%20because%20the%20two%20car%20styles%20access%20the%20back%20seat%20very%20differently.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ELikewise%2C%20Windows%20Calculator%20and%20Excel%20do%20things%20differently%2C%20and%20there%20is%20nothing%20we%20can%20do%20to%20change%20that%2C%20AFAIK.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EExcel%20uses%2064-bit%20binary%20floating-point%20(IEEE%20754%20standard)%20to%20store%20values.%20Intel-compatible%20CPUs%20use%20an%2080-bit%20binary%20floating-point%20representation%20for%20intermediate%20calculations.%20But%20Excel%20rounds%20pairwise%20operations%20to%2064-bit%20BFP.%20(In%20contrast%20to%20VBA%2C%20which%20uses%20the%2080-bit%20intermediate%20results%20sometimes.)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAccording%20to%20a%20wikipage%2C%20Windows%20Scientific%20Calculator%20used%20to%20do%20the%20same%20thing.%20But%20since%20Win%2098%20(!)%2C%20the%20claim%20is%20that%20Win%20Sci%20Calc%20uses%20%22bignum%22%20precision%20(nearly%20unbounded)%20for%20%22basic%22%20operations%2C%20and%20%2232%20digit%20precision%22%20for%20other%20operations.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20say%20%22claim%22%20because%20Win%20Sci%20Calc%20(for%20my%20Win%207)%20__displays__%20only%20%2232%20digit%20precision%22%20for%20all%20operations.%20For%20example%2C%20123456789012345678901234567890*123456789012345678901234567890%20displays%201.5241578753238836750495351562536e%2B58%20(32-digit%20mantissa)%20instead%20of%20the%20exact%2059-digit%20integer.%20However%2C%20when%20we%20subtract%201.5241578753238836750495351562536e%2B58%2C%20the%20calculator%20displays%20198787501905199875019052100%2C%20which%20suggests%20that%20it%20does%20__store__%20more%20precision%20than%20it%20displays.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E-----%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732989%22%20target%3D%22_blank%22%3E%40Shahid81%3C%2FA%3E%26nbsp%3B%20wrote%3A%20%60%60I%20am%20looking%20that%20what%20logic%20is%20used%20by%20Excel%20(i.e%20Truncate%2C%20Round%2C%20RoundUp%20or%20RoundDown)%20after%20the%2015%20decimals%60%60%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EJust%20to%20fine-tune%20your%20understanding%20of%20Excel....%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E1.%20When%20we%20__enter__%20numbers%20manually%20(or%20read%20them%20from%20a%20text%20file)%2C%20Excel%20truncates%20after%2015%20significant%20digits%2C%20replacing%20any%20digits%20to%20the%20right%20with%20zero.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E2.%20When%20Excel%20__displays__%20(or%20formats)%20a%20numerical%20value%2C%20it%20arbitrarily%20rounds%20to%2015%20significant%20digits%2C%20replacing%20any%20digit%20positions%20to%20the%20right%20with%20zero.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E3.%20As%20I%20noted%20above%2C%20Excel%20uses%2064-bit%20binary%20floating-point%20(IEEE%20754%20standard)%20to%20store%20values%20in%20memory.%20That%20represents%20values%20as%20a%20sum%20of%2053%20consecutive%20powers%20of%202%20(%22bits%22).%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EConsequently%2C%20most%20decimal%20fractions%20(and%20integers%20larger%20than%202%5E53)%20cannot%20be%20represented%20exactly.%20And%20the%20binary%20approximation%20of%20a%20particular%20decimal%20fraction%20might%20vary%20depending%20on%20the%20magnitude%20of%20the%20number.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThat%20is%20why%2C%20for%20example%2C%20IF(10.01%20-%2010%20%3D%200.01%2C%20TRUE)%20returns%20FALSE(!).%20If%20we%20format%2010.01%20-%2010%20to%20display%2016%20decimal%20places%2C%20we%20can%20see%20the%20difference%3A%20it%20displays%200.00999999999999979.%20The%20difference%20arises%20because%20of%20the%20different%20approximations%20of%201%2F100%20for%2010.01%20and%200.01.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E4.%20Moreover%2C%20Excel%20is%20inconsistent%20in%20its%20treatment%20of%20some%20calculations%20and%20comparisons.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESometimes%2C%20Excel%20arbitrarily%20(and%20inconsistently)%20substitutes%20the%20actual%20binary%20difference%20at%20the%20end%20of%20a%20formula%20with%20exacty%20zero%20(0.00E%2B00)%20if%20the%20operands%20are%20%22close%20enough%22.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EConsequently%2C%20%3DA1-A2%20might%20result%20in%20exactly%20zero%20(0.00E%2B00)%2C%20but%20%3DA1-A2%3D0%20might%20return%20FALSE(!)%20and%20MATCH(A1%2CA2%2C0)%20might%20return%20%23N%2FA%2C%20indicating%20that%20their%20binary%20values%20are%20not%20actually%20the%20same.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ELikewise%2C%20SUM(A1%3AA2)%20might%20return%20exactly%20zero%2C%20but%20SUM(A1%2C--A2)%20might%20return%20an%20infinitesimal%20result.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAlso%2C%20the%20Excel%20comparison%20operators%20(%22%3D%22%2C%20%22%26gt%3B%22%2C%20etc)%20round%20the%20left%20and%20right%20operands%20to%2015%20significant%20decimal%20digits%2C%20but%20only%20for%20the%20purpose%20of%20the%20comparison.%20Other%20methods%20of%20comparison%20(e.g.%20lookup%20functions)%20compare%20the%20exact%20binary%20values.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EConsequently%2C%20%3DA1-A2%20might%20display%20an%20infinitesimal%20difference%20(e.g.%202.22E-16)%2C%20but%20%3DA1%3DA2%20might%20return%20TRUE(!).%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThese%20inconsistencies%20are%20not%20due%20to%20compliance%20with%20the%20IEEE%20754%20standard.%20They%20are%20unique%20to%20Excel%20(and%20any%20compatible%20products)%2C%20a%20misguided%20attempt%20to%20hide%20some%20of%20the%20anomalies%20that%20arise%20due%20to%20the%20binary%20approximation%20of%20decimal%20fractions.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E5.%20Contrary%20to%20most%20explanations%2C%20Excel%20calculations%20are%20__not__%20limited%20to%2015%20significant%20decimal%20digits.%20Nor%20does%20Excel%20%22store%22%20only%20up%20to%2015%20significant%20decimal%20digits.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E(Anyone%20who%20insists%20that%20it%20does%20lacks%20common%20sense.%20If%20Excel%20did%20use%20only%2015%20signficant%20decimal%20digits%2C%20how%20could%2010.01%20-%2010%20not%20be%20exactly%200.01%3F%20Rhetorical.)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%20fact%2C%20Excel%20uses%20up%20to%20__17__%20significant%20decimal%20digits%20to%20represent%20values%20when%20they%20are%20stored%20in%20XML%20files%2C%20which%20is%20how%20%22xlsx%22%20and%20%22xlsm%22%20are%20saved%20(in%20a%20compressed%20zip%20file).%20But%20that%20is%20because%20the%20IEEE%20754%20standard%20states%20that%20up%20to%2017%20significant%20digits%20are%20necessary%20and%20sufficient%20in%20order%20to%20convert%20between%20a%20decimal%20approximation%20and%20the%20exact%20binary%20value%20with%20no%20loss%20of%20precision.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIt%20is%20important%20to%20note%20that%20even%20the%2017-digit%20representation%20is%20an%20approximation.%20In%20fact%2C%20the%20exact%20decimal%20approximation%20of%20many%20binary%20values%20has%20many%20more%20decimal%20digits%2C%20even%20100s.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ETo%20demonstrate%2C%20note%20that%20the%20result%20of%20%3D59.8-30%20appears%20as%2029.799999999999997%20(17%20digits)%20in%20an%20%22xlsx%22%20file.%20But%20%3D59.8-30-29%20(and%20%3D59.8-59)%20appears%20as%200.79999999999999716%20(17%20digits).%20Where%20did%20the%20%22...16%22%20come%20from%3F%20(Rhetorical.)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%20fact%2C%20the%20exact%20decimal%20approximation%20of%2059.8%20is%2059.7999999999999%2C971578290569595992565155029296875.%20The%20exact%20decimal%20approximation%20of%2059.8-30%20is%2029.7999999999999%2C971578290569595992565155029296875.%20And%20the%20exact%20decimal%20approximation%20of%2059.8-59%20is%200.799999999999997%2C1578290569595992565155029296875.%20Those%20are%20all%20copacetic.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%20contrast%2C%20the%20exact%20decimal%20approximation%20of%2029.8%20is%2029.8000000000000%2C00710542735760100185871124267578125.%20And%20the%20exact%20decimal%20approximation%20of%200.8%20is%200.800000000000000%2C0444089209850062616169452667236328125.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E(I%20use%20period%20for%20the%20decimal%20point%20and%20comma%20to%20demarcate%20the%20first%2015%20significant%20digits.)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi all respectable members,

 

I am having an issue while calculating some values in Microsoft Excel.

I've an equation where result is more than 15 decimals. In such cases, Excel is not showing the decimals after 15 decimals. I've come to know that its the normal behaviour of Excel.

However, I am looking that what logic is used by Excel (i.e Truncate, Round, RoundUp or RoundDown) after the 15 decimals.

 

For better explanation of the issue, I am posting some values :-

 

Example 1

Excel = 5.8/1200 + 1 = 1.00483333333333000000

Scientific Calculator = 5.8/1200 + 1 = 1.0048333333333333333333333333333

 

In above, it seems that Excel is Truncating the values

Example 2

Excel = (5.8/1200 + 1) ^ -1         = 0.9951899154088570

Calculator = (5.8/1200 + 1) ^ -1 = 0.99518991540885719024713882899323

In above it seems that excel is Truncating values after 15 decimals. 

Here the difference is also due to the difference result of (5.8/1200+1) ^ -1 in Excel and calculator.

Example 3

Excel = (5.8/1200 + 1) ^ -2         = 0.9904029677314890

Calculator = (5.8/1200 + 1) ^ -2 = 0.99040296773148832998554204245467

In above, it seems that excel is Rounding Up, instead of Truncating.

 

Bottom Line:-

I am looking forward a logic which I can use in Calculator or in My code, which helps to give results identical to Excel.

i.e.

Step 1 : First changing the values of 5.8/1200 + 1, which gives result identical to the Excel

Step 2 : Then changing the values of (5.8/1200 + 1) ^ any number, gives identical result to Excel.

 

Thanks in advance.

0 Replies