Inconsistency error in the MROUND function

%3CLINGO-SUB%20id%3D%22lingo-sub-1188612%22%20slang%3D%22en-US%22%3EInconsistency%20error%20in%20the%20MROUND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188612%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20I%20have%20found%20an%20error%20in%20the%20MROUND%20function.%20The%20error%20occurs%20when%20the%20number%20to%20be%20rounded%20is%2025575%20and%20the%20integer%20is%2050.%20In%20this%20case%2C%20MROUND%20is%20supposed%20to%20return%2025600.%20However%2C%20sometimes%20it%20returns%2025550%20instead.%26nbsp%3B%20Specifically%2C%20MROUND(25575%3B50)%20%3D%2025600%2C%20but%20MROUND(25000*1.023%3B50)%20%3D%2025500%2C%20even%20though%26nbsp%3B25000*1.023%20%3D%26nbsp%3B25575.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAccording%20to%20the%20Office%20Support%20page%20for%20MROUND%2C%22%3CSPAN%3EMROUND%20rounds%20up%2C%20away%20from%20zero%2C%20if%20the%20remainder%20of%20dividing%20number%20by%20multiple%20is%20greater%20than%20or%20equal%20to%20half%20the%20value%20of%20multiple%22%3C%2FSPAN%3E.%20In%20this%20case%20the%20remainder%20is%20abs(25575-25600)%3Dabs(25575-25575)%3D25.%20As%2025%2F50%3D0.5%2C%20MROUND%20is%20supposed%20to%20round%20up.%20However%2C%20as%20shown%20in%20the%20above%20paragraph%2C%20whether%20or%20not%20MROUND%20rounds%20down%20or%20up%2C%20may%20depend%20on%20whether%20the%20input%20is%20a%20string%20number%20or%20a%20product%20of%20to%20factors.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20inconsistency%20does%20not%20happen%20independently%20of%20input%20numbers.%20For%20the%20numbers%20I've%20tried%2C%20there%20is%20only%20the%20obove%20case%20in%20which%20the%20error%20occurs.%20The%20Excel%20versions%20of%20which%20this%20error%20has%20occured%20is%20Excel%20365%20and%20Excel%202016.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20an%20Excel-file%20that%20shows%20the%20issue.%20Link%20to%20Office%20Support%20page%20for%20MROUND%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fmround-function-c299c3b0-15a5-426d-aa4b-d2d5b3baf427%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60470%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fmround-function-c299c3b0-15a5-426d-aa4b-d2d5b3baf427%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60470%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1188612%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-1188759%22%20slang%3D%22en-US%22%3ERe%3A%20Inconsistency%20error%20in%20the%20MROUND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188759%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545174%22%20target%3D%22_blank%22%3E%40Fleischbender%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInteresting%2C%20to%20be%20sure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20yourself%20say%20it%20doesn't%20happen%20all%20the%20time%2C%20even%20with%20the%20numbers%20you%20cite.%3C%2FP%3E%3CP%3E%3CEM%3E%22However%2C%20%3CU%3E%3CSTRONG%3Esometimes%3C%2FSTRONG%3E%3C%2FU%3E%20it%20returns%2025550%20instead%22%3C%2FEM%3E%3C%2FP%3E%3CP%3EEmphasis%20added.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20no%20doubt%20run%20out%20extended%20decimal%20places%20as%20I%20just%20did%20to%20see%20if%20somehow%20there's%20a%20digit%20added%20at%20the%20end.....If%20indeed%20it's%20only%20%22sometimes%22%20that%20this%20aberration%20occurs%2C%20it%20may%20just%20be%20a%20function%20of%20%22static%22%20in%20the%20system.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20also%20significant%20(from%20a%20diagnostic%20point%20of%20view)%20that%20you've%20not%20been%20able%20to%20produce%20this%20error%20with%20any%20other%20numbers.%20Given%20that%20256%20is%20one%20of%20those%20numbers%20in%20the%20binary%20sequence--2%2C4%2C8%2C16%2C32%2C64%2C128%2C256%2C512%20etc--I'm%20wondering%20if%20that%20in%20and%20of%20itself%20is%20a%20possible%20causative%20factor.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189965%22%20slang%3D%22en-US%22%3ERe%3A%20Inconsistency%20error%20in%20the%20MROUND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189965%22%20slang%3D%22en-US%22%3EIndeed%20I%20have%20tried%20to%20subtract%20the%20%E2%80%9C25550%20answer%E2%80%9D%20from%20the%20%E2%80%9C25600%20answer%E2%80%9D%20and%20multiply%20the%20difference%20by%2010%5E(1%20gorillion).%20The%20difference%20is%20still%200%2C%20so%20no%20floating%20point%20or%20reoccurring%20remainder%20induced%20errors%20here.%3CBR%20%2F%3E%3CBR%20%2F%3EAnother%20funny%20occurrence%20happens%20when%20you%20copy%20a%20cell%20containing%2025000*1%2C023%20and%20paste%20the%20value%2C%20ie.%20the%20pure%20string%2025575%2C%20into%20another%20cell.%20If%20you%20now%20take%20MROUND%20of%20this%20cell%20it%20returns%20the%20wrong%20number%2025550.%20However%2C%20if%20you%20now%20clear%20the%20cell%20and%20type%2025575%20by%20hand%2C%20the%20MROUND%20formula%20suddenly%20returns%20the%20correct%20number%2025600.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I think I have found an error in the MROUND function. The error occurs when the number to be rounded is 25575 and the integer is 50. In this case, MROUND is supposed to return 25600. However, sometimes it returns 25550 instead.  Specifically, MROUND(25575;50) = 25600, but MROUND(25000*1.023;50) = 25500, even though 25000*1.023 = 25575. 

 

According to the Office Support page for MROUND,"MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple". In this case the remainder is abs(25575-25600)=abs(25575-25575)=25. As 25/50=0.5, MROUND is supposed to round up. However, as shown in the above paragraph, whether or not MROUND rounds down or up, may depend on whether the input is a string number or a product of to factors. 

 

This inconsistency does not happen independently of input numbers. For the numbers I've tried, there is only the obove case in which the error occurs. The Excel versions of which this error has occured is Excel 365 and Excel 2016.

 

Attached is an Excel-file that shows the issue. Link to Office Support page for MROUND: https://support.office.com/en-us/article/mround-function-c299c3b0-15a5-426d-aa4b-d2d5b3baf427?NS=EXC...

2 Replies
Highlighted

@Fleischbender 

 

Interesting, to be sure.

 

You yourself say it doesn't happen all the time, even with the numbers you cite.

"However, sometimes it returns 25550 instead"

Emphasis added.

 

You've no doubt run out extended decimal places as I just did to see if somehow there's a digit added at the end.....If indeed it's only "sometimes" that this aberration occurs, it may just be a function of "static" in the system.

 

It's also significant (from a diagnostic point of view) that you've not been able to produce this error with any other numbers. Given that 256 is one of those numbers in the binary sequence--2,4,8,16,32,64,128,256,512 etc--I'm wondering if that in and of itself is a possible causative factor.

 

 

Highlighted
Indeed I have tried to subtract the “25550 answer” from the “25600 answer” and multiply the difference by 10^(1 gorillion). The difference is still 0, so no floating point or reoccurring remainder induced errors here.

Another funny occurrence happens when you copy a cell containing 25000*1,023 and paste the value, ie. the pure string 25575, into another cell. If you now take MROUND of this cell it returns the wrong number 25550. However, if you now clear the cell and type 25575 by hand, the MROUND formula suddenly returns the correct number 25600.