8 Consecutive points rising or falling

[joecrabtree]

To all,

I have created a dynamic Statistical process control chart, and now
wish to do the following ( If possible ):

1) Highlight in red any 8 consectutive points that are rising or 8
consecutive points that are falling.
2) Highlight in blue 8 consecutive points on the same side of the mean.
( Above or below ).

Thanks in advance for any advice/help.

Joseph Crabtree

 

[Jon Peltier]

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:B12>B4: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:B12>B4: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

 

[joecrabtree]

Hi

Thanks that worked really well. Just one question regarding the
plotting of the data:

1) I now have an x axis column, a data column ( list of numbers ), and
a third column that says weather it is increasing or decreasing etc.

What is the best way to plot this column third column ontop of my
existing data?

Thanks for your help,

Regards

Joseph Crabtree

 

[Jon Peltier]

Assuming you already have a chart with the first two columns, go to Source
Data on the Chart menu, click on the Series tab, click Add series, then
select the same X values range and the calculated Y values range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi

Thanks that worked really well. Just one question regarding the
plotting of the data:

1) I now have an x axis column, a data column ( list of numbers ), and
a third column that says weather it is increasing or decreasing etc.

What is the best way to plot this column third column ontop of my
existing data?

Thanks for your help,

Regards

Joseph Crabtree