Counting Consecutive Cells that meet Criteria

[Omega Point]

Hi,

I have a list of numbers for each weekday going back about 4 years and
I want to be able to count the number of times there are two, three,
etc. consecutive days with positive and negative numbers. I then want
to be able to perform basic statistical functions on these numbers.
Here's an example of some of the data:

Jan 1, 2006 $.10
Jan 2, 2006 $.20
Jan 3, 2006 -$.15
Jan 4, 2006 -$.05
Jan 5, 2006 -$.07
Jan 6, 2006 $.12
Jan 7, 2006 -$.03
Jan 8, 2006 $.06
Jan 9, 2006 $.04
Jan 10, 2006 -$.09

I want to count the number of times there are two, three, etc.
consecutive days that there are positive and negative numbers. For
example, in the data above the number of two consecutive days of
positive occurrences would be 2 (Jan 1 and Jan 2 and then again on Jan
8 and Jan 9).

Next, I want to sum the numbers that meet the criteria and determine
averages.

How do I do this in Excel?

Thanks for your help.

 

[rsenn]

There are probably fancier ways using the OFFSET function, but if you
could create formulas in the next column based on the IF function.

IF( AND (A10 < 0, A11 < 0, A12 < 0), do this, do that)

It looks like you are trying to find out how to get rich by predicting
stock price movements. Let us know if you figure it out.

 

[Biff]

Hi!

Here's one way, but it takes several steps to get the desired results.

Assuming there are no empty cells within the range.

Since you're testing for 2 different conditions, consecutive positives and
consecutive negatives, you'll need to do it as separate operations for each
condition.

Assuming the numeric values are in the range B2:Bn. Row 1 are the column
headers and do not contain any numeric values.

For the consecutive positives:

Enter this formula in C2:

=IF(AND(B2>0,B1>0),SUM(C1,1),1)

Enter this formula in D2:

=IF(AND(B3>0,B2>0),D3,C2)

Select both C2 and D2 and copy down to the end of the data set.

Now, to count how many times a positive number appears in 2 consecutive
cells:

=COUNTIF(C2:Cn,2)

To get the average where 2 consecutive cells are positive:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(D2n=2,B2:Bn))

For negatives it's the same except you need to change the logical operators
in the formulas in C2 and D2 from greater than >, to less than <. (or use a
different range of cells for those calcs)

Biff

"Omega Point" <[email protected]>
wrote in message
news:[email protected]...