Frequency bug?
Can someone explain to me why this happens?
This example is working.
FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and 0.2) are equal to or less than 0.2. 1 value (0.3) is larger than 0.2 and equal or smaller than 0.3. 1 value (0.4) is larger than 0.3.
This example is not working as I thought it would.
FREQUENCY function returns {2; 0; 2} and I donยดt understand why?
The formula in cell B8:B11 returns this array {0.1; 0.2; 0.3; 0.4}, exactly the same values as in B2:B5.
It seems to be the MOD function but why?
Why am I using the MOD function? To extract the fractional part of a number.
Download workbook
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this [โฆ]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 [โฆ]
How to count unique distinct values based on a date
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. [โฆ]
Find the longest/shortest consecutive sequence of a value
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell [โฆ]
The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo [โฆ]
Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I [โฆ]
The quotient function returns the integer portion of a division. Example, 5/2 = 2.5. The integer is 2. Excel Function [โฆ]
Rearrange cells in a cell range to vertically distributed values
The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct [โฆ]
5 Responses to โFrequency bug?โ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
Hmm, seems like it's half Frequency's fault and half Mod's fault.
=MOD(1.3,1) causes the issue, but =MOD(2.3,1) does not. Yet =MOD(1.3,1)=0.3 returns true. Very interesting catch!
The same thing happens if I use INT function to extract the decimal part of a number.
Like this:
=1.1-INT(1.1)
=1.2-INT(1.2)
=1.3-INT(1.3)
=1.4-INT(1.4)
Both Excel 2010 and 2013 yields the same output.
[โฆ] Cronquist is having a problem with the FREQUENCY function, when combined with the MOD function. Can you explain the [โฆ]
It's our old friend the floating point error.
https://www.office-loesung.de/p/viewtopic.php?f=166&t=693238#p2874564
MOD(ROW(A13)/10,1)=0,3 is TRUE but
(MOD(ROW(A13)/10,1)-0,3) is 5,55111512312578E-17 but
MOD(ROW(A13)/10,1)-0,3 is 0
So, ROUND(MOD(ROW(11:14)/10,1),1) should work.
XLarium,
Thank you for commenting, I didn't know this.
I found this:
https://support.microsoft.com/en-us/kb/78113
That explains why this happens.