Range Matrix

G

GarToms

Hi all,
I am looking to display the following data into a matrix. Does anyon
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
GarTom
 
L

Leo Heuser

GarToms said:
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
Click to expand...



Hi GarToms

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

In D8:

=IF($C8>D$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.
 
Top