## 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

### Frequency

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, position)+ Ctrl + Shift + Enter Udf in cell E3:E30: =FreqWords(B2:C11, 1)+ Ctrl + Shift + Enter How to create […]

Introduction What is unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count unique distinct values within same week Formula in B3: =WEEKNUM(C3) + ENTER Array formula in E3: =SUM(--((FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))>0)) […]

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 […]

### Mod

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo operation (link to wikipedia). MOD(number, divisor) Number - The number for which you want to find the remainder. Divisor - The […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I have the following situation: A1, B1, C1, D1, E1, F1 where A1 = nick b1 = 10 c1 = zack […]

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 […]

### 5 Responses to “Frequency bug?”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**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.

**Contact Oscar**

You can contact me through this webpage

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.

http://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.