# Transition Matrix? How do they work?

1. ## Transition Matrix? How do they work?

Hello Excelusers,

for work I have to create a transition matrix - I was hoping you could help a rat-race participant's life a little bit easier. Problem / situation is detailed below. Preferred method of solving this problem would be using the Pivot Table function in Excel 2007.

Here is the transition matrix:
X axis = Old Group
Y axis = New Group
X = no change in Groups

.. 1 2 3 4 5 6 7
1 X - - - - - -
2 - X - - - - -
3 - - X - - - -
4 - - - X - - -
5 - - - - X - -
6 - - - - - X -
7 - - - - - - X

Here is the data I need to plot on the transition matrix:
....................... TOM .......... JIM ......... SUE
Old Group: ........ 7 ............ 6 ............ 4
New Group: ...... 5 ............ 4 ............ 5

Any idea on how to get excel to automatically fill in the matrixes based on a table with iformation on Tom, Jim, Sally and Sue?

In the end the transition matrix should look like this: (T = Tom, J = Jim and S = Sue)

.. 1 2 3 4 5 6 7
1 X - - - - - -
2 - X - - - - -
3 - - X - - - -
4 - - - X - J -
5 - - - S X - T
6 - - - - - X -
7 - - - - - - X

2. ## Re: Transition Matrix? How do they work?

Perhaps using an array formula. See attached doc.

Beau Nydal