+ Reply to Thread
Results 1 to 7 of 7

Creating a transition matrix

  1. #1
    Registered User
    Join Date
    08-11-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Creating a transition matrix

    transition matrix.xlsx

    hi, in the attached file is some data and a transition matrix created based on the data, its been created by a website i use.

    i want to be able to create it myself using excel but im not sure what formulas/functions would be best to use.

    many thanks

    H

  2. #2
    Registered User
    Join Date
    08-10-2013
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Creating a transition matrix

    From the data sheet and the matrix provided there appear to be other calculations being performed using other data.

    I assume you're familiar with those calculations and will be able to deal with them once you have the data in the matrix.

    What I've done is named ranges in the data set to make writing formulas used to tabulate the data simpler. If your data set will vary greatly from what you posted, I'd suggest increasing the length of the named ranges to simplify future operations.

    The formulas from the DATA sheet have been copied into the Matrix page. The COUNTIFS funciton allows counting based on multiple criteria, which is great for this application.

    Hope this helps you get closer to what you need.
    Attached Files Attached Files

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,075

    Re: Creating a transition matrix

    Hi and welcome to the forum

    If I understand you correctly, try this in C2, copied down and across...
    =COUNTIFS(data!$D:$D,RIGHT('transition matrix'!C$1,1),data!$B:$B,'transition matrix'!$A2)

    I dont quite know what you are looking for in column B though?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    08-11-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Creating a transition matrix

    Quote Originally Posted by Phenarole View Post
    From the data sheet and the matrix provided there appear to be other calculations being performed using other data.

    I assume you're familiar with those calculations and will be able to deal with them once you have the data in the matrix.

    What I've done is named ranges in the data set to make writing formulas used to tabulate the data simpler. If your data set will vary greatly from what you posted, I'd suggest increasing the length of the named ranges to simplify future operations.

    The formulas from the DATA sheet have been copied into the Matrix page. The COUNTIFS funciton allows counting based on multiple criteria, which is great for this application.

    Hope this helps you get closer to what you need.
    thank you soo much, thats really helped me alot.

    just wanted to ask, how did you set the range and rename it as kslevel or grade

    =COUNTIFS(KSLEVEL,3,GRADE,"G")

    =COUNTIFS(E2:E266,2,G2:G266,"G") i dont know how to change E2:E266 to show as kslevel like you have, my knowledge is limited.

    many thanks again

    H

  5. #5
    Registered User
    Join Date
    08-11-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Creating a transition matrix

    thanks for you help/reply. but im gonna try first solution, i can understand the code easier. my excel skills are limited and looking at you code got me but thanks for taking time out to reply.

  6. #6
    Registered User
    Join Date
    08-10-2013
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Creating a transition matrix

    "just wanted to ask, how did you set the range and rename it as kslevel or grade

    =COUNTIFS(KSLEVEL,3,GRADE,"G")

    =COUNTIFS(E2:E266,2,G2:G266,"G") i dont know how to change E2:E266 to show as kslevel like you have, my knowledge is limited."


    It's easy to create a named range. Open a spreadsheet and click in any cell. Look to the left of the formula bar and you'll see a box displaying the cell's location. That box is called the "name box". You can enter any name your like in it and use that name in formulas to refer to the value in that cell.

    You can see all user defined names in a drop-down list by clicking the arrow at the right end of the name box or by opening the Name Manager icon in the Formula bar. The Name Manager is useful for creating or redefining names and ranges as well as deleting previously created ranges.

    Appropriately named ranges in formulas are certainly an aid in understanding what data is being referenced by a given function (and they are less susceptible to typos).

  7. #7
    Registered User
    Join Date
    08-11-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Creating a transition matrix

    Quote Originally Posted by Phenarole View Post
    "just wanted to ask, how did you set the range and rename it as kslevel or grade

    =COUNTIFS(KSLEVEL,3,GRADE,"G")

    =COUNTIFS(E2:E266,2,G2:G266,"G") i dont know how to change E2:E266 to show as kslevel like you have, my knowledge is limited."


    It's easy to create a named range. Open a spreadsheet and click in any cell. Look to the left of the formula bar and you'll see a box displaying the cell's location. That box is called the "name box". You can enter any name your like in it and use that name in formulas to refer to the value in that cell.

    You can see all user defined names in a drop-down list by clicking the arrow at the right end of the name box or by opening the Name Manager icon in the Formula bar. The Name Manager is useful for creating or redefining names and ranges as well as deleting previously created ranges.

    Appropriately named ranges in formulas are certainly an aid in understanding what data is being referenced by a given function (and they are less susceptible to typos).
    thanks for that, i didnt know you could do that before but now i do

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using a transition matrix as input to a cummulative matrix
    By Walter12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2012, 04:19 AM
  2. How to calculate frequency transition matrix in Excel?
    By sally123 in forum Excel General
    Replies: 1
    Last Post: 03-27-2012, 06:35 AM
  3. I need help creating a 3x3 Matrix in vba?
    By mattm99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2011, 03:36 PM
  4. Transition Matrix? How do they work?
    By excelpadwan in forum Excel General
    Replies: 1
    Last Post: 08-24-2011, 10:19 AM
  5. Creating a matrix from columns
    By Ernie Sersen in forum Excel General
    Replies: 2
    Last Post: 02-18-2005, 10:06 AM

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