# Creating a transition matrix

1. ## 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. ## 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.

3. ## 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?

4. ## Re: Creating a transition matrix

Originally Posted by Phenarole
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(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. ## 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. ## Re: Creating a transition matrix

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

=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. ## Re: Creating a transition matrix

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

=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