Forum Discussion

HarishVangala's avatar
HarishVangala
Copper Contributor
Jun 16, 2019
Solved

FREQUENCY() bug or floating point error?

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...
excel
Formulas and Functions
office 365
training
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jun 17, 2019

    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.

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 16, 2019

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.

Resources