SOLVED
Home

FREQUENCY() bug or floating point error?

%3CLINGO-SUB%20id%3D%22lingo-sub-694725%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency()%20BUG%3F%3F%20(screenshot%20at%20the%20end)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694725%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361084%22%20target%3D%22_blank%22%3E%40HarishVangala%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECongratulations.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F78113%2Ffloating-point-arithmetic-may-give-inaccurate-results-in-excel%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EYou%20just%20discovered%20the%20floating%20point%20error.%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DPZRI1IfStY0%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETom%20Scott%20explaining%20floating%20point%20error%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694750%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency()%20BUG%3F%3F%20(screenshot%20at%20the%20end)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20floating%20point%20error%2C%20but%20it's%20not%20clear%2C%20at%20least%20for%20me%2C%20why%20in%20FREQUENCY.%20Equivalent%20formulas%20like%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIFS(%24A%241%3A%24A%2430%2C%22%26lt%3B%3D%22%26amp%3BC8%2C%24A%241%3A%24A%2430%2C%22%26gt%3B%22%26amp%3BC7)%20or%0A%3DCOUNTIFS(%24A%241%3A%24A%2430%2C%22%26lt%3B%3D%22%26amp%3BC8%3AC14%2C%24A%241%3A%24A%2430%2C%22%26gt%3B%22%26amp%3BC7%3AC13)%20%20as%20CSE%0A%0A%3DSUMPRODUCT((%24A%241%3A%24A%2430%26lt%3B%3DC8)*(%24A%241%3A%24A%2430%26gt%3BC7))%3C%2FPRE%3E%0A%3CP%3Eetc.%2C%20all%20return%20correct%20result%20on%20this%20sample%2C%20and%20only%20FREQUENCY%20is%20affected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694823%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency()%20BUG%3F%3F%20(screenshot%20at%20the%20end)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20for%20this%20could%20be%20how%20the%20numbers%20are%20processed%20in%20the%20different%20functions.%3C%2FP%3E%3CP%3EAt%20least%20COUNTIFS()%20is%20known%20to%20cut%20numbers%20after%2015%20decimal%20places.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAh%2C%20found%20something%20in%20my%20archive%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fexcel-tips%2F17-or-15-digits-of-precision%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E17%20or%2015%20digits%20of%20precision%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DWZfjmbEDbfI%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECalc%20Bug%20Due%20to%2017%20Digits%20of%20Precision%3C%2FA%3E%3C%2FP%3E%3CP%3EThanks%20to%20MrExcel%20for%20solving%20the%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-697116%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency()%20BUG%3F%3F%20(screenshot%20at%20the%20end)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-697116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361084%22%20target%3D%22_blank%22%3E%40HarishVangala%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20save%20the%20workbook%20and%20look%20into%20the%20XML%20data%20I%20see%20numbers%20like%20this%3A%3C%2FP%3E%3CP%3E1.1000000000000001%3C%2FP%3E%3CP%3E1.3000000000000003%3C%2FP%3E%3CP%3E1.5000000000000004%3C%2FP%3E%3CP%3E2.2999999999999998%3C%2FP%3E%3CP%3E3.9000000000000026%3C%2FP%3E%3CP%3EDefinitely%20floating%20point%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698396%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency()%20BUG%3F%3F%20(screenshot%20at%20the%20end)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDetlef%2C%20thank%20you%20for%20the%20links%20(and%20Bill%20with%20Jan%20Karel%20for%20the%20investigation%20and%20explanation%20of%20the%20issue)%2C%20I%20had%20no%20idea%20about%20this%20before.%20Here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsfmagazine.com%2Fpost-entry%2Faugust-2017-excel-rank-countif-and-floating-point-errors%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsfmagazine.com%2Fpost-entry%2Faugust-2017-excel-rank-countif-and-floating-point-errors%2F%3C%2FA%3E%26nbsp%3Bis%20bit%20more%20in%20conclusion%20part.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%2C%20using%20of%20different%20precision%20for%20SORT%2C%20RANK%20and%20FREQUENCY%20is%20kind%20of%20design%20bug.%20As%20well%20as%20considering%201900%20as%20leap%20year%20and%20wrong%20using%20of%20negation%20with%20exponentiation%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fmathforum.org%2Flibrary%2Fdrmath%2Fview%2F69058.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fmathforum.org%2Flibrary%2Fdrmath%2Fview%2F69058.html%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694709%22%20slang%3D%22en-US%22%3EFREQUENCY()%20bug%20or%20floating%20point%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694709%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20this%20is%20an%20easy-to-reproduce%20bug%20with%20frequency()%20function%20in%20Excel.%20Please%20also%20look%20at%20the%20screenshot%20at%20the%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E----%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CEM%3EPPS%3A%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3EAs%20the%20Dellef_Lewin%20confirmed%20in%20his%20response%20below%20towards%20the%20end%2C%20this%20seems%20to%20be%20the%20issue%20of%20floating%20point%20numbers.%20%3C%2FEM%3E%3CEM%3EThe%20problem%20is%20that%20even%20the%20long-format%20display%20mechanism%20to%20display%20more%20decimals%20via%20format-%26gt%3Bnumber-%26gt%3Bdecimal%20places%20is%20not%20correct.%20So%20the%20only%20way%20is%20that%20we%20must%20go%20to%20the%20underlying%20XML%20files%20and%20verify%20the%20numbers%20explicitly.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIn%20summary%2C%20this%20is%20not%20the%20infamous%20fundamental%20precision%20problems%20with%20floating%20point%20numbers.%20This%20is%20the%20problem%20with%20the%20%3CSTRONG%3Edesign%20inconsistency%3C%2FSTRONG%3E%20among%20EXCEL's%20basic%20functions%3A%20IF()%2C%20SORT()%2C%20RANK()%20and%20in%20the%20latest%2C%20the%20FREQUENCY()%20in%20terms%20of%20the%20number%20of%20decimal%20digits%20that%20are%20handled%20accurately.%20For%20example%2C%20IF()%20and%20its%20family%20handle%2015%20decimal%20digits%20only%2C%20where%20as%20FREQUENCY%20and%20others%20handle%2017%20decimal%20digits.%20So%20when%20IF%20says%20two%20numbers%20are%20equal%2C%20SORT()%20or%20FREQUENCY()%20may%20give%20opposite%20%26amp%3B%20surprising%20results.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPS%3A%3C%2FSTRONG%3E%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EI%20have%20already%20anticipated%20the%20error%20with%20the%20%3CU%3Efloating%20point%20numbers%20%26amp%3B%20their%20accuracy%3C%2FU%3E%20in%20this%20post%2C%20and%20I%20think%20that%20it%20is%20not%20the%20case%20here.%20When%20I%20displayed%20the%20numbers%20with%2030%20decimal%20digits%20precision%20on%20excel%20itself%2C%20I%20see%20that%20the%20numbers%20are%20equal.%20Note%20that%20eventhough%20IF()%20comparison%20confirms%20the%20same%20when%20compared%2C%20the%20IF()%20is%20known%20to%20have%20issues%20by%20itself).%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E----%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EBasically%2C%20I%20entered%20a%20series%20of%20numbers%201%2C1.1%2C...%2C3%20using%20the%20following%20steps%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3E%26nbsp%3BEnter%201.0%20in%20cell%20A1%3C%2FLI%3E%3CLI%3E%26nbsp%3BEnter%20the%20formula%20(without%20quotes)%20%22%3DA1%2B0.1%22%20in%20cell%20A2.%3C%2FLI%3E%3CLI%3E%26nbsp%3BFill%20cells%20A3%3AA30%20by%20fill%20handle%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ECreate%20the%20bins%20in%20another%20column%20using%20the%20following%20steps%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3E%26nbsp%3B1.0%20in%20cell%20C8%2C%201.5%20in%20cell%20C9%2C%202.0%20in%20cell%20C10%2C%202.5%20in%20cell%20C11%2C%203.0%20in%20cell%20C12%2C%203.5%20in%20cell%20C13%2C%204.0%20in%20cell%20C14%3C%2FLI%3E%3CLI%3E%26nbsp%3BSelect%20cells%20D8%3AD15%20and%20press%20F2%2C%20to%20enter%20the%20formula%20(without%20quotes)%20%22%3Dfrequency(A1%3AA30%2CC8%3AC14)%22%20and%20finally%20press%20Ctrl%2BShift%2BEnter%3C%2FLI%3E%3CLI%3E%26nbsp%3BWhile%20the%20result%20is%20supposed%20to%20be%201%2C5%2C5%2C5%2C5%2C5%2C4%2C%20you%20will%20notice%20a%20discrepancy%20as%20you%20may%20also%20verify%20from%20the%20screenshot%20below.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENotes%20on%20debugging%20attempts%20%26amp%3B%20possible%20causes%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20note%20that%20a%20non-extreme%20bin%20is%20formed%20with%20elements%20X%20such%20that%20%3CEM%3Elow%20%26lt%3B%20X%20%26lt%3B%3D%20high.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3E%26nbsp%3BThe%20problem%20is%20only%20with%20the%20data%20values%20that%20match%20the%20bin%20boundaries.%20Which%20often%20happens%20in%20cases%2C%20for%20example%2C%20currency%20data%20or%20class%20marks%20and%20so%20on.%3C%2FLI%3E%3CLI%3E%26nbsp%3BBecause%20the%20data%20is%20generated%20using%20a%20mathematical%20formula%2C%20and%20the%20frequency%20bins%20are%20entered%20using%20direct%20entry%2C%20the%20values%20are%20different%20in%20the%20hidden%20least%20significant%20decimal%20places.%20As%20a%20result%2C%20when%20the%20bins%20are%20tested%20for%20less%20%26amp%3B%20equal%2C%20the%20value%20may%20fall%20in%20the%20wrong%20bin%2C%20resulting%20in%20the%20wrong%20counts.%3C%2FLI%3E%3CLI%3E%26nbsp%3BBut%20if%20this%20is%20the%20correct%20reason%2C%20I%20should%20see%20the%20mismatched%20decimal%20places%20when%20I%20tried%20to%20display%20in%20long%20form%20with%2030%20decimal%20places%20%26amp%3B%20scientific%20notation.%20But%20I%20see%20absolutely%20no%20difference%20in%20the%20number%20(see%20in%20the%20screenshot)%3C%2FLI%3E%3CLI%3E%26nbsp%3BFurther%2C%20I%20performed%20a%20conditional%20test%20%3DIF(A21%3DC12%2C%22YES%2CEqual%22%2C%22No%2C%20not%20equal%22)%20and%20excel%20returns%20that%20they%20are%20equal.%3C%2FLI%3E%3CLI%3E%26nbsp%3BFinally%2C%20I%20explicitly%20typed%203.0%20(instead%20of%20the%20existing%20formula)%20to%20overwrite%20the%20visibly%20same%20entry%20in%20A21.%20And%20the%20wrong%20count%20disappeared%20and%20the%20counting%20error%20continued%20to%20next%20boundary.%20If%20I%20overwrite%20the%20entry%203.5%20by%20typing%20it%20out%20explicitly%20(instead%20of%20the%20existing%20formula)%2C%20and%20go%20on%20doing%20it%20for%20every%20bin%20boundary%2C%20the%20counts%20are%20correctly%20visible.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENow%20can%20someone%20kindly%20explain%20where%20the%20problem%20is%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EMy%20intention%20is%20to%20generate%20a%20neat%20histogram%20by%20myself%20after%20this%20basic%20use%20of%20frequency()%20function%20to%20generate%20the%20values%20on%20x-axis%20and%20y-axis.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20numbers%2C%20when%20calculated%20using%20the%20mathematically%20identical%20formula%20(%3DA1%2B0.1%20etc.)%20are%20different%20from%20the%20same%20result%20I%20type%20them%20explicitly!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20913px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118228iB64B57C86243BDD3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Book1%20-%20Excel%2016_06_2019%2010_57_45%20PM.png%22%20title%3D%22Book1%20-%20Excel%2016_06_2019%2010_57_45%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-694709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694956%22%20slang%3D%22en-US%22%3ERe%3A%20Frequency()%20BUG%3F%3F%20(screenshot%20at%20the%20end)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694956%22%20slang%3D%22en-US%22%3E%3CP%3E(EDITED)%3C%2FP%3E%3CP%3EIs%20this%20a%20floating%20point%20error%20really%3F%20because%20when%20I%20checked%20numbers%20displayed%20in%2030%20precision%20digits%2C%20it%20shows%20correctly!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProbably%20looking%20at%20the%20underlying%20XML%20files%20is%20the%20only%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20to%20%40Detlef_Lewin%20for%20confirming.%20I%20can%20consider%20the%20alternative%20workarounds%20that%20use%2015%20digits%20only.%3C%2FP%3E%3C%2FLINGO-BODY%3E
HarishVangala
New Contributor

I think this is an easy-to-reproduce bug with frequency() function in Excel. Please also look at the screenshot at the end.

 

----

PPS:

As the Dellef_Lewin confirmed in his response below towards the end, this seems to be the issue of floating point numbers. The problem is that even the long-format display mechanism to display more decimals via format->number->decimal places is not correct. So the only way is that we must go to the underlying XML files and verify the numbers explicitly.

 

In summary, this is not the infamous fundamental precision problems with floating point numbers. This is the problem with the design inconsistency among EXCEL's basic functions: IF(), SORT(), RANK() and in the latest, the FREQUENCY() in terms of the number of decimal digits that are handled accurately. For example, IF() and its family handle 15 decimal digits only, where as FREQUENCY and others handle 17 decimal digits. So when IF says two numbers are equal, SORT() or FREQUENCY() may give opposite & surprising results.

 

PS: @Detlef Lewin @Sergei Baklan 

I have already anticipated the error with the floating point numbers & their accuracy in this post, and I think that it is not the case here. When I displayed the numbers with 30 decimal digits precision on excel itself, I see that the numbers are equal. Note that eventhough IF() comparison confirms the same when compared, the IF() is known to have issues by itself).

----

 

Basically, I entered a series of numbers 1,1.1,...,3 using the following steps:

 

  1.  Enter 1.0 in cell A1
  2.  Enter the formula (without quotes) "=A1+0.1" in cell A2.
  3.  Fill cells A3:A30 by fill handle

 

Create the bins in another column using the following steps:

 

  1.  1.0 in cell C8, 1.5 in cell C9, 2.0 in cell C10, 2.5 in cell C11, 3.0 in cell C12, 3.5 in cell C13, 4.0 in cell C14
  2.  Select cells D8:D15 and press F2, to enter the formula (without quotes) "=frequency(A1:A30,C8:C14)" and finally press Ctrl+Shift+Enter
  3.  While the result is supposed to be 1,5,5,5,5,5,4, you will notice a discrepancy as you may also verify from the screenshot below.

 

Notes on debugging attempts & possible causes:

First of all, note that a non-extreme bin is formed with elements X such that low < X <= high.

 

  1.  The problem is only with the data values that match the bin boundaries. Which often happens in cases, for example, currency data or class marks and so on.
  2.  Because the data is generated using a mathematical formula, and the frequency bins are entered using direct entry, the values are different in the hidden least significant decimal places. As a result, when the bins are tested for less & equal, the value may fall in the wrong bin, resulting in the wrong counts.
  3.  But if this is the correct reason, I should see the mismatched decimal places when I tried to display in long form with 30 decimal places & scientific notation. But I see absolutely no difference in the number (see in the screenshot)
  4.  Further, I performed a conditional test =IF(A21=C12,"YES,Equal","No, not equal") and excel returns that they are equal.
  5.  Finally, I explicitly typed 3.0 (instead of the existing formula) to overwrite the visibly same entry in A21. And the wrong count disappeared and the counting error continued to next boundary. If I overwrite the entry 3.5 by typing it out explicitly (instead of the existing formula), and go on doing it for every bin boundary, the counts are correctly visible.

 

Now can someone kindly explain where the problem is?

My intention is to generate a neat histogram by myself after this basic use of frequency() function to generate the values on x-axis and y-axis.

 

The numbers, when calculated using the mathematically identical formula (=A1+0.1 etc.) are different from the same result I type them explicitly!

 

Book1 - Excel 16_06_2019 10_57_45 PM.png

 

6 Replies
Why can't floating point do money? It's a brilliant solution for speed of calculations in the computer, but how and why does moving the decimal point (well, in this case binary or radix point) help and how does it get currency so wrong? 3D Graphics Playlist: ...

@Detlef Lewin 

 

That's floating point error, but it's not clear, at least for me, why in FREQUENCY. Equivalent formulas like

=COUNTIFS($A$1:$A$30,"<="&C8,$A$1:$A$30,">"&C7) or
=COUNTIFS($A$1:$A$30,"<="&C8:C14,$A$1:$A$30,">"&C7:C13)  as CSE

=SUMPRODUCT(($A$1:$A$30<=C8)*($A$1:$A$30>C7))

etc., all return correct result on this sample, and only FREQUENCY is affected.

@Sergei Baklan 

The reason for this could be how the numbers are processed in the different functions.

At least COUNTIFS() is known to cut numbers after 15 decimal places.

 

Ah, found something in my archive:

17 or 15 digits of precision

Calc Bug Due to 17 Digits of Precision

Thanks to MrExcel for solving the case.

 

(EDITED)

Is this a floating point error really? because when I checked numbers displayed in 30 precision digits, it shows correctly!

 

Probably looking at the underlying XML files is the only way.

 

Thanks to @Detlef_Lewin for confirming. I can consider the alternative workarounds that use 15 digits only.

Solution

@HarishVangala 

When I save the workbook and look into the XML data I see numbers like this:

1.1000000000000001

1.3000000000000003

1.5000000000000004

2.2999999999999998

3.9000000000000026

Definitely floating point error.

 

@Detlef Lewin 

 

Detlef, thank you for the links (and Bill with Jan Karel for the investigation and explanation of the issue), I had no idea about this before. Here https://sfmagazine.com/post-entry/august-2017-excel-rank-countif-and-floating-point-errors/ is bit more in conclusion part.

 

In general, using of different precision for SORT, RANK and FREQUENCY is kind of design bug. As well as considering 1900 as leap year and wrong using of negation with exponentiation http://mathforum.org/library/drmath/view/69058.html