What I generally do is put a formula into a helper column to identify
special points.
1) Highlight in red any 8 consectutive points that are rising or 8
consecutive points that are falling.
This formula in C12 looks back at 8 intervals for a streak of consecutively
increasing or decreasing points:
{=IF(AND(B5:B12B4:B11),"all increasing",IF(AND(B5:B12<B4:B11),"all
decreasing",""))}
Array formula: hold CTRL+SHIFT while pressing ENTER. This puts a label into
the cell in (for example) column C; to plot these points, use the value in
B12 instead, or NA() instead of "", and plot column C atop column B:
{=IF(AND(B5:B12B4:B11),B12,IF(AND(B5:B12<B4:B11), B12,NA()))}
2) Highlight in blue 8 consecutive points on the same side of the mean.
( Above or below ).
Put one of these this array formulas (Ctrl+Shift+Enter) into D12:
{=IF(AND(B5:B12$D$2),"above average streak",IF(AND(B5:B12<$D$2),"below
average streak",""))}
{=IF(AND(B5:B12$D$2),B12,IF(AND(B5:B12<$D$2),B12, NA()))}
where $D$2 contains the average.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______