FREQUENCY (and maybe floating-point-problem)

K

KaiKai

Board Regular
Joined
May 8, 2008
Messages
50
Hi everybody!

Something mysterious is going on here...

<table valign="middle" colspan="9" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="62,4pt"><col width="62,4pt"><col width="62,4pt"><col width="62,4pt"><col width="62,4pt"><col width="62,4pt"><col width="62,4pt"><col width="62,4pt"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td><td align="middle">G</td><td align="middle">H</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">old data</td><td align="left">new data (1)</td><td align="left">new data (2)</td><td align="right">
</td><td align="left">data</td><td align="left">frequency (1)</td><td align="left">frequency (2)</td><td align="left">countif</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="right">1001</td><td align="right">1,001</td><td align="right">1,001</td><td align="right">
</td><td align="right">1,001</td><td align="right">0</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="right">1002</td><td align="right">1,002</td><td align="right">1,002</td><td align="right">
</td><td align="right">1,002</td><td align="right">2</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="right">1003</td><td align="right">1,003</td><td align="right">1,003</td><td align="right">
</td><td align="right">1,003</td><td align="right">0</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="right">1004</td><td align="right">1,004</td><td align="right">1,004</td><td align="right">
</td><td align="right">1,004</td><td align="right">2</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td align="right">1005</td><td align="right">1,005</td><td align="right">1,005</td><td align="right">
</td><td align="right">1,005</td><td align="right">0</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td align="right">1006</td><td align="right">1,006</td><td align="right">1,006</td><td align="right">
</td><td align="right">1,006</td><td align="right">2</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td align="right">1007</td><td align="right">1,007</td><td align="right">1,007</td><td align="right">
</td><td align="right">1,007</td><td align="right">0</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">9</td><td align="right">1008</td><td align="right">1,008</td><td align="right">1,008</td><td align="right">
</td><td align="right">1,008</td><td align="right">2</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">10</td><td align="right">1009</td><td align="right">1,009</td><td align="right">1,009</td><td align="right">
</td><td align="right">1,009</td><td align="right">0</td><td align="right">1</td><td align="right">1</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>B2</td><td>=A2*0.001</td></tr><tr><td>C2</td><td>=A2/1000</td></tr><tr><td>F2</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G2</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H2</td><td>=COUNTIF(B$2:B$10,E2)</td></tr><tr><td>B3</td><td>=A3*0.001</td></tr><tr><td>C3</td><td>=A3/1000</td></tr><tr><td>F3</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G3</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H3</td><td>=COUNTIF(B$2:B$10,E3)</td></tr><tr><td>B4</td><td>=A4*0.001</td></tr><tr><td>C4</td><td>=A4/1000</td></tr><tr><td>F4</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G4</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H4</td><td>=COUNTIF(B$2:B$10,E4)</td></tr><tr><td>B5</td><td>=A5*0.001</td></tr><tr><td>C5</td><td>=A5/1000</td></tr><tr><td>F5</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G5</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H5</td><td>=COUNTIF(B$2:B$10,E5)</td></tr><tr><td>B6</td><td>=A6*0.001</td></tr><tr><td>C6</td><td>=A6/1000</td></tr><tr><td>F6</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G6</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H6</td><td>=COUNTIF(B$2:B$10,E6)</td></tr><tr><td>B7</td><td>=A7*0.001</td></tr><tr><td>C7</td><td>=A7/1000</td></tr><tr><td>F7</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G7</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H7</td><td>=COUNTIF(B$2:B$10,E7)</td></tr><tr><td>B8</td><td>=A8*0.001</td></tr><tr><td>C8</td><td>=A8/1000</td></tr><tr><td>F8</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G8</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H8</td><td>=COUNTIF(B$2:B$10,E8)</td></tr><tr><td>B9</td><td>=A9*0.001</td></tr><tr><td>C9</td><td>=A9/1000</td></tr><tr><td>F9</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G9</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H9</td><td>=COUNTIF(B$2:B$10,E9)</td></tr><tr><td>B10</td><td>=A10*0.001</td></tr><tr><td>C10</td><td>=A10/1000</td></tr><tr><td>F10</td><td>{=FREQUENCY(B2:B10,E2:E10)}</td></tr><tr><td>G10</td><td>{=FREQUENCY(C2:C10,E2:E10)}</td></tr><tr><td>H10</td><td>=COUNTIF(B$2:B$10,E10)</td></tr></tbody></table><table style="font-family: Arial; font-size: 8pt; background-color: rgb(255, 255, 255);"><tbody><tr><td style="font-weight: bold;">Achtung, Matrixformel enthalten!</td></tr><tr><td>Die geschweiften Klammern{} werden nicht eingegeben.</td></tr><tr><td>Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.</td></tr></tbody></table>

As you can see, I have some data in A2:A10.
In two different ways, this data is divided by 1000 in columns B and C.
In column E the new data is listed again (manually).
Column F is weird, while column G is obviously correct.

Okay, this might be another floating-point-problem, I thought...

But: In column H COUNTIF works very fine with the data from column B!

So: Where's the crux?
Can you help me?

edit: Excel 2010
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
What do you get if you type 1001, 1002, 1003 etc into a range of new cells, and then compare to your column A figures, say using subtraction?
 
Upvote 0
Hi Glenn!

The data in column A was typed in manually, so a formula like =A2=1001 returns TRUE of course.
 
Upvote 0
Well, I can't reproduce your error ... that's Excel 2007 by the way.
 
Upvote 0
Hi Glenn!

Ahhh, ok! Thank you for this information!
Maybe MS will fix that with a service-pack soon...:laugh:
 
Upvote 0
KaiKai said:
Hi Glenn!

Ahhh, ok! Thank you for this information!
Maybe MS will fix that with a service-pack soon...:laugh:
Click to expand...
Ha ha, yeah, I'm sure a lot of service packs will be supplied for the 2010 version.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
F
  • Question Question
circular reference - query
Replies
8
Views
282
fayez_MrExcel
F
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
M
  • Solved
FILTER Function & MAP/LAMBDA through Different Ranges & Arrays
Replies
1
Views
353
Fluff
Fluff
C
  • Question Question
Data Entry and Report Generation
Replies
2
Views
321
Carla carla
C

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top