Forum Discussion
Frequency Formula in office 365
Hi! I need some help with this function. When I press enter, the formula returns 7 values instead of 6 (because of the range of cells for the matrix, that actually is 6).
What’s the problem here?
Thanks :))
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.
-----
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.
That is by design. From the official documentation for the FREQUENCY function:
"The number of elements in the returned array is one more than the number of elements in bins_array. The extra element in the returned array returns the count of any values above the highest interval. For example, when counting three ranges of values (intervals) that are entered into three cells, be sure to enter FREQUENCY into four cells for the results. The extra cell returns the number of values in data_array that are greater than the third interval value."
The problem is that the formula you show returns 12 numbers and not 7.
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.
-----
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.
JoeUser2004 Thank you so much. It worked!!!
