Statistical analysis of text

[motoroller]

I've got a spreadsheet which has data in the first few columns, then another couple of columns which have formulae for calculations. The last column says "profit/loss" where I have two values based on an if statement: if(L3>0, "P", "L&quot. What I want to do is analyse the trends in profit/loss - so I have a sequence that goes P P L L L L P L L P P P L L L L. How can I get excel to analyse these and put the values in a statistical distribution? I want to get statistics on how long each profit/loss session lasts. That is, I want a distribution that says:

Days profit Frequency
1 1
2 1
3 1
4 0
5 0
etc., and another for losses.

[DonkeyOte]

You can use a Frequency Array

Assume for sake of demo that

a) your sequence is located in C2:C17

b) your day list (say 1 to 7) is located in E2:E8 with results to be listed in adjacent cells

You can get the frequencies of the durations with:

Please Login or Register  to view this content.

Arrays are expensive so keep ranges as lean as possible else consider use of lightweight helper calcs to negate need for Array in first instance.