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 within same week, month or year
Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]5 Responses to “Frequency bug?”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
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.