+ Reply to Thread
Results 1 to 2 of 2

Transition Matrix? How do they work?

  1. #1
    Registered User
    Join Date
    08-24-2011
    Location
    Cubicleland, Anywhere
    MS-Off Ver
    Excel 2003
    Posts
    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.
    Thank you in advance.

    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
    Last edited by excelpadwan; 08-24-2011 at 05:19 AM.

  2. #2
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Transition Matrix? How do they work?

    Perhaps using an array formula. See attached doc.

    Beau Nydal
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts