SOLVED

Exel, division by 0

%3CLINGO-SUB%20id%3D%22lingo-sub-1555058%22%20slang%3D%22en-US%22%3EExel%2C%20division%20by%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3ESorry%2C%20my%20native%20language%20is%20Russian.%3CBR%20%2F%3E%3C%2FSTRONG%3E%3CSTRONG%3EThis%20message%20was%20sent%20to%20Microsoft%20Technical%20Support.%20I%20was%20directed%20here%20to%20ask%20a%20question%20about%20my%20mistake.%3C%2FSTRONG%3E%3CBR%20%2F%3EI%20am%20using%20Microsoft%20Exel%202010%2C%20composing%20the%20formula%20for%20the%20calculation%2C%20I%20accidentally%20divided%20by%200%2C%20but%20Exel%20did%20not%20reveal%20that%20it%20was%200%20and%20even%20more%20he%20gave%20the%20answer.%20I%20thought%20this%20error%20might%20propagate%20to%20older%20versions%20of%20Exel%2C%20so%20I%20went%20to%20Exel%20Online%2C%20I%20was%20surprised%20to%20find%20that%20this%20error%20still%20works.%20If%20we%20enter%20the%20formula%20(1%20-%20((0.052%20*%20225)%20%2F%20(0.9%20%2B%200.048%20*%20225)))%2C%20then%20in%20the%20answer%20we%20get%200%2C%20and%20if%20we%20take%20a%20number%2C%20for%20example%2C%20it%20will%20be%207%2C%20then%20writing%20the%20formula%207%20%2F%20(1-%20(%20(0.052%20*%20225)%20%2F%20(0.9%20%2B%200.048%20*%20225)))%20then%20in%20the%20answer%20we%20get%206.30504E%20%2B%2016%20or%2063050394783186900.%20By%20all%20the%20rules%20of%20mathematics%2C%20you%20cannot%20take%20a%20number%20and%20divide%20by%200.%20I%20have%20a%20link%20to%20a%20division%20by%200%20table%20in%20Exel%20Online%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AnRqZg99QBnia3bap9yOVyKZ9U0%3Fe%3DPUuw46%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AnRqZg99QBnia3bap9yOVyKZ9U0%3Fe%3DPUuw46%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1555058%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-1555163%22%20slang%3D%22en-US%22%3ERe%3A%20Exel%2C%20division%20by%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744443%22%20target%3D%22_blank%22%3E%40Woory%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20caused%20by%20tiny%20rounding%20errors%20that%20occur%20when%20converting%20from%20decimal%20to%20binary%20and%20back.%3C%2FP%3E%0A%3CP%3EThe%20formula%20%3D1-((0.052*225)%2F(0.9%2B0.048*225))%3D0%20returns%20FALSE%2C%20and%20the%20formula%20%3D1-((0.052*225)%2F(0.9%2B0.048*225))-0%20returns%200.000000000000000111022302462516.%3C%2FP%3E%0A%3CP%3ESince%20the%20numbers%20in%20your%20formula%20are%20accurate%20to%203%20decimal%20places%2C%20you%20can%20avoid%20the%20problem%20by%20rounding%20the%20denominator%20to%203%20decimal%20places%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DA1%2FROUND(1-((0.052*225)%2F(0.9%2B0.048*225))%2C3)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Sorry, my native language is Russian.
This message was sent to Microsoft Technical Support. I was directed here to ask a question about my mistake.
I am using Microsoft Exel 2010, composing the formula for the calculation, I accidentally divided by 0, but Exel did not reveal that it was 0 and even more he gave the answer. I thought this error might propagate to older versions of Exel, so I went to Exel Online, I was surprised to find that this error still works. If we enter the formula (1 - ((0.052 * 225) / (0.9 + 0.048 * 225))), then in the answer we get 0, and if we take a number, for example, it will be 7, then writing the formula 7 / (1- ( (0.052 * 225) / (0.9 + 0.048 * 225))) then in the answer we get 6.30504E + 16 or 63050394783186900. By all the rules of mathematics, you cannot take a number and divide by 0. I have a link to a division by 0 table in Exel Online https://1drv.ms/x/s!AnRqZg99QBnia3bap9yOVyKZ9U0?e=PUuw46

1 Reply
Best Response confirmed by Woory (Occasional Visitor)
Solution

@Woory 

 

This is caused by tiny rounding errors that occur when converting from decimal to binary and back.

The formula =1-((0.052*225)/(0.9+0.048*225))=0 returns FALSE, and the formula =1-((0.052*225)/(0.9+0.048*225))-0 returns 0.000000000000000111022302462516.

Since the numbers in your formula are accurate to 3 decimal places, you can avoid the problem by rounding the denominator to 3 decimal places:

 

=A1/ROUND(1-((0.052*225)/(0.9+0.048*225)),3)