Counting Multiple Ranges over set Threshold in one cell range

[Davey Peter]

Hi all I am looking for a formula that is going to count all of the consecutive months equal to or over a set threshold (in this case the threshold is $10). The goal of this project is to count how long capital is invested in a project before it is paid back.

My problem arises in accounts such as "Widget 2" of the attached when I have invested capital that is paid back but then reinvest capital further down the road. If I do a countif(cells over threshold) I get 7. The problem with this I am trying to find out the average amount of time money is invested before it is returned so in the attached "Widget 1" has money invested for 4 months (equal to or over threshold) and in "Widget 2" money is invested for 3 months and then paid back and then invested again for 4 months before being paid back. Ideally I would like a formula that will return 4 for "Widget 1" and somehow return both 3 and 4 for "Widget 2"


If it is not possible to have a formula that is going to return 2 answers (3 & 4 in the case of "Widget 2") it would still be valuable if I could count the range of the first time the investment breaches the threshold before it is paid back (In the case of "Widget 2" the answer would be 3 as the money was first invested at or above the threshold 3/1/2010 and was invested for 3 months before being paid back down below the threshold.)

A1 B C D E F G H I J K L M N O 2 Threshold $10 3 1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010 6/1/2010 7/1/2010 8/1/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 4 Widget 1 0 0 2 10 15 20 10 5 5 0 0 0 Count: 4 5 Widget 2 0 5 10 15 10 2 0 10 15 15 10 5 Count 3 & 4 <tbody> </tbody>

Thank you,

<tbody>
</tbody>