Get excel to create groups of data based on cell values

Question

I have a question that has me stumped. I feel like I am very close to the answer, but cant ever create an adequate solution. I have tried nested IF statements, incorporating the AND function, incorporating the OR function, and I cant come up with something robust enough to answer this question

Here is my question

I am creating a program that automatically analyzes a stock using a oscillator equation. The results of these equations (in theory) predict how the stock will perform over the near term.

In this equations formula, a stock is considered underbought if the calculated oscillator is below 30 and overbought if it is above 80. I would like to group the cells by the contents of the calculated oscillator value.

The beginning of the period starts with the first instance of a value at or below 30 following the previous occurrence of a value at or over 80.

The period is closed with the first occurrence of a value at or over 80 occurring after the previously identified =<30 value.

There will be times that a value doesn't fit in to a period. For example, a value of 50 sandwiched between the end of the last period and the beginning of the next period will not have a group.

I have attached 2 pictures.

The first picture is a example of what the data could look like. The %D column is the calculated oscillator result. It is important to note that I would like to extend this solution to multiple stocks each having varying data. I will be using it to analyze stocks over varying times (say a 6 month period or a 12 month period). Therefore, the solution needs to be dynamic enough to fit varying amounts of cells and different distributions of data.

The second picture is a example of what I would like the result to look like. I have added a comments section to explain the reasoning behind the grouping in case my description above didn't make sense.

Image 1

Image 2

Please let me know if I can clarify anything for you. Any ideas or assistance is GREATLY appreciated

Answer 1

Put this in B2 and copy down:

=IF(AND(NOT(ISNUMBER(B1)),A2<=30),MAX($B$1:B1)+1,IF(MAX($B$1:B1)=0,"",IF(AND(ISNUMBER(B1),A1>=80),"",B1)))