Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
8 Consecutive points rising or falling
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
8 Consecutive points rising or falling
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 _______ |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
8 Consecutive points rising or falling
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 On Dec 4, 5:52 pm, "Jon Peltier" wrote: 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 Solutionshttp://PeltierTech.com _______ |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
8 Consecutive points rising or falling
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" wrote in message oups.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 On Dec 4, 5:52 pm, "Jon Peltier" wrote: 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 Solutionshttp://PeltierTech.com _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can this be done using an array formula ? | Excel Worksheet Functions | |||
Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
hlookup function | Excel Worksheet Functions | |||
Can this adjusted GPA formual be accomplised? | Excel Discussion (Misc queries) | |||
Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) |