Frequency Formula in office 365

HansVogelaar  wrote: ``That is by design. [....] "The number of elements in the returned array is one more than the number of elements in bins_array [....]"

Yes, that is true.  But if there is no way to limit that in modern versions of Excel, that is a flaw in those versions, IMHO.

In the old days (e.g. Excel 2010), yes, the FREQUENCY function returns an array that is one more than the number of bins (second parameter).

But if we select a range equal to only the number of bins (or less) when we array-enter the =FREQUENCY(...) formula, FREQUENCY can (and will) return only that many elements.

Just like any other array value -- in old versions of Excel, at least.

-----

Ezequiel_Elma 

That said, one work-around (*) would be to specify =FREQUENCY(A3:A49, E16:E25).  IOW, a bin range that is purposely one less than the actual bin range.

Then, FREQUENCY adds the extra row in G26, without regard for the "bin value" in E26.

In fact, sometimes I take advantage of that and make the last bin label a string like ">6.65", which is truly my intent.

TMI....  And if want to rely on numeric values in the bin range (e.g. for look-ups elsewhere), I would enter 7.15 into E26, but I might use the custom format ">6.65" (!).

But of course, you can simply enter or calculate 7.15 as you did.

-----

(*) Of course, that "work-around" does not work if your intent is to purposely exclude values in A3:A49 that are greater than the last bin value, namely 7.15.  If that were the case, there are other ways to accomplish that, albeit not as easily.