Make a crosstable with the frequency of numbers

[Stoney1]

Hello,

In the example i attached i wrote down rows with numbers in it. I would like to make a crosstable that counts the frequency in which the numbers occur in the same row.

So in the sheet 'Overzicht' you have the rows with numbers for the months 'januari' and 'februari'.

In the sheets januari and februari you have these crosstables, with already a formula in it that counts the frequency in which the number occur in the same row.

But there has to be a easier way to do this?

[karedog]

Hi,

Here's my try to optimize your formulas.
I create a new sheet name NewJanuari as a modification to you Januari sheet.

Some of the improvements :

First :
In Januari sheet, the formula at B2 :
=COUNTIF(Overzicht!$A$2:$F$6,Matrix!B2)

but actually the value of Matrix!B2 is exactly the value of cell above B2, that is B1.
So it doesn't need to refer to Matrix sheet at all, just replace with this code :

Please Login or Register  to view this content.

Second :
The formulas in table area (B17:AT61) do very much recalculations for the same thing.
For example, when you compare frequency between number 2 and 3, you do countif() for number 2 and countif() for number 3. But then in the next cell, you compare between 2 and 4, so the 2 is recalculated again, and so on. So for the same thing, you recalculate again and again.
It is better to save these countif() for each numbers and then if we need them, just refer to this saved precalculated countif. So I put these precalculated countif() in range A5:AT9 in NewJanuari sheet.


Third :
Please be noticed that if the table area is divided into two triangles using right down diagonal (imaginary line formed with cells marked with "/"), one triangle is exactly a mirror of another one, so you just need to calculate one triangle, the other one just need to copy the result from its mirror. It is understandable because when you compare 1 with 2, the result is same as compare 2 with 1, 7 and 9 will be the same as 9 and 7, etc.

So the formula in B17 is :

=IF($A17=B$16,"/" --> if the cell compares same number than show "/"

,IF(ROW()-ROW($A$16)>COLUMN()-COLUMN($A$16),OFFSET($A$16,COLUMN()-COLUMN($A$16),ROW()-ROW($A$16)) --> if the cell is at the bottom triangle, don't calculate, but use the top triangle mirror value instead

,MIN(OFFSET($A$4,1,$A17),OFFSET($A$4,1,B$16))+MIN(OFFSET($A$4,2,$A17),OFFSET($A$4,2,B$16))+MIN(OFFSET($A$4,3,$A17),OFFSET($A$4,3,B$16))+MIN(OFFSET($A$4,4,$A17),OFFSET($A$4,4,B$16))+MIN(OFFSET($A$4,5,$A17),OFFSET($A$4,5,B$16)))) --> this is formula for cells located at upper triangle using precalculated countif


The final formula in B17 is :

Please Login or Register  to view this content.