8 Consecutive points rising or falling

  • Thread starter Thread starter joecrabtree
  • Start date Start date
J

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
 
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.
Click to expand...

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 ).
Click to expand...

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
 
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
 
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
_______


joecrabtree said:
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
Click to expand...
 
Back
Top