Thread: COUNTIF to determine "Transitions"

Hi,

I uploaded an example file here, it consists of several rows of numbers from 1-4:
http://rapidshare.com/files/442388079/notekeysB101.xlt


Now what I need from this sequences of numbers, is the average transition percentage (from any cell to the neighboring cell to the right of it) for
1->1, 1->2, 1->3, 1->4, 2->1, 2->2, 2->3,... etc. (16 possibilities).

I guess this can be done with a countif command, but somehow I can't get it to work when I select criterias involving two cells...
Is there a way around it?

Ideally, I would need just a set of 16 formulas, that I can copy to other files because I must evaluate dozens of these worksheets. Also note, the number of rows and columns vary.

Can you upload example excel workbook here, on forum?

I'm not sure what you exactly want (or where)...

But, since you take all possibilities won't number of those transactions be COUNT(column) - 1 ?

It's not very clear in terms of where you want the results ?

If the results are meant to be conducted line by line or using the block in it's entirety ?

If the latter it's not clear if the first value of row below counts as a transition from the last number present two rows above ?

Using your sample... assuming analysis of entire set but where each line is separate from the last (ie no "rolling transition")

A49:A64
1.1 to 4.4 (stored as numbers - decimal so perhaps 1,1 to 4,4 for you)

Assuming then use of XL2007 (implied by profile if not by file)

B49:
=COUNTIFS($B$2:$BY$46,INT($A49),$C$2:$BZ$46,ROUND(10*MOD($A49,1),0))
copied down
(again change all delimiters as necessary)

the above would generate a breakdown of the 988 transitions that take place:

1.1	52
1.2	47
1.3	47
1.4	47
2.1	50
2.2	119
2.3	73
2.4	53
3.1	50
3.2	75
3.3	121
3.4	55
4.1	41
4.2	52
4.3	61
4.4	45

If you are not using XL2007 then you can not use COUNTIFS - let us know.

Originally Posted by lexluthor

count column doesn't work here, these numbers are in no specific order, they are just random measurements.

I have no idea what the above comment relates to I'm afraid - it has no relevance to the formula I posted.

Originally Posted by lexluthor

as an example: row two is a particular time period where 43 consecutive states were measured. It starts with a 3 and goes into a 4. just by looking at it, this transition happens only once here, so the percentage 3-->4 would be 1/43.
now I want to do this for all 12 possible transitions between the 4 states and averaged over all rows.

Surely there are 2 transitions from 3 to 4 on row 2 - B2:C2 and AD2:AE2 ?

In the results I posted 3.4 is 55 - this means across all rows there are 55 transitions from 3 to 4, on a row by row basis:

2
1
2
0
2
3
2
3
5
1
3
2
1
3
4
2
2
4
4
2
2
0
5

My point re: analysis... you shed no light in terms of the level of granularity - analyse rows separately / analyse all rows simultaneously (per my example)

If you want a prompt answer it's generally a good idea to post expected results based on sample data - this way people can see

a) where you want the results

b) the logic behind calculating the results

You also have yet to confirm which version you will be using this with - that will directly impact the possible solutions.

Does this do what you want (and my apology if it duplicates DO's suggestion).

EDIT: Looking at DO's last post, I expect it does.

I just put together the below which uses XL2007 specific approaches (as an alternative to above)

edit: @shg, not really, I originally suggested a set of 12 to process all rows simultaneously - I suspect OP wants row by row which is what both of our attachments demo.

Originally Posted by lexluthor

edit: oh sorry I was not clear about that, in fact I want it over all rows averaged, but thats one last step I can do on my own.

this was the approach covered in post # 5

omg, I just found a huge flaw in my thinking...

the transition probabilities should of course not be the percentage of all 12 cases, but rather 1.1/ (1.1+1.2+1.3+1.4),i.e., just from the 4 possible paths that it can go from state 1, etc.

anyway, your solutions are still very useful, a minor change in the code is needed,
in post #9 it needs to be changed for example into:
=IF($CA3="";CB2/SUM($CB2:$CE2);COUNTIFS($B3:$BY3;INT(CB$1);$C3:$BZ3;RIGHT(CB$1)))


,thanks for the help

If I've understood - see attached (v2)