Hi all,
I am looking to display the following data into a matrix. Does anyone know how I can achieve this by formula or an Excel function.

DATA
A....50
B....150
C....60
D....200

MATRIX (count of data)
.........0......100......200
0.......X.........2........4
100....X.........0........2
200....X.........X........1

Cheers in advance
GarToms

"GarToms" skrev i en meddelelse
...

Hi all,
I am looking to display the following data into a matrix. Does anyone
know how I can achieve this by formula or an Excel function.

DATA
A....50
B....150
C....60
D....200

MATRIX (count of data)
........0......100......200
0.......X.........2........4
100....X.........0........2
200....X.........X........1

Cheers in advance
GarToms



Hi GarToms

One way assuming data in A2:D2 and matrix in C7:F10

In D8:

=IF($C8D$7,"X",SUMPRODUCT(($A$2:$D$2=$C8)*($A$2: $D$2<=D$7)))

Please notice the use of mixed absolute and relative
adresses in $C8 and D$7, which makes it possible to
do the following copying.

Copy D8 to E8:F8 with the fill handle (the little square
in the lower right corner of the cell)

Copy D8:F8 to D9:F10 with the fill handle.

If your dataset never contains 0 (zero) enter
X manually in D9.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

If your dataset never contains 0 (zero) enter
X manually in D9.

Should of course have been D8