Complicated "IF" Statement Problem

[rwmill9716]

I'm writing a program to evaluate the properties of "runs rules" commonly used for Shewhart Charts. I have a series of 5,000 normally distributed means in col A. Col C equals 1 if the row mean falls below the distribution mean (40 from Cell B29) and 0 otherwise. Similarly, Col D equals 1 or 0 depending on whether the row mean falls above the population mean or not.

My runs rule is defined in Cells F26 and H26 as being 4 the last 8 in this case.

Col E cells are defined by the following formula:

=IF(SUM(INDIRECT("D"&ROW(D47)-$H$26+1):D47)=$F$26, IF(SUM(INDIRECT("E"&ROW(E46)-$F$26):E46)=0, 1, IF(SUM(INDIRECT("C"&ROW(C47)-$H$26+1):C47)=$F$26, IF(SUM(INDIRECT("E"&ROW(E46)-$F$26):E46)=0,1, 0),0)),0)

I'm trying to sum the last 8 rows in cols C and D to see if there are four 1s in either column over that period. Further, I'm summing the Col E entries to ensure that the col E signals are reset after a signal is given. That is, I don't want to get a series of consecutive 1's in Col E.

While my formula works for the Col C data, none of the signals for the Col D data are being picked up. For example, there should be a 1 in Cell E69.

My question is this: Is there a better approach to accomplishing want I'm trying to do (perhaps with and "OR" statement), or can my formula be fixed?

Thanks,

Ric

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #C0C0C0;;">Runs Rules:</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">of</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"> > or < µ</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">of</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"> > 1s</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="font-weight: bold;text-align: center;background-color: #C0C0C0;;">Population</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">of</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"> > 2s</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;border-right: 1px solid black;background-color: #C0C0C0;;">µ =</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">40</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Shift</td><td style="text-align: center;background-color: #C0C0C0;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #C0C0C0;;">+</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">of</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"> > 3s</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;border-right: 1px solid black;background-color: #C0C0C0;;">s =</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">0</td><td style="border-left: 1px solid black;background-color: #C0C0C0;;">ss</td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;border-right: 1px solid black;background-color: #C0C0C0;;">1s limits=</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">37</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;background-color: #FFFF00;;">43</td><td style="text-align: center;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;border-right: 1px solid black;background-color: #C0C0C0;;">2s limits =</td><td style="text-align: center;border-left: 1px solid black;background-color: #FFFF00;;">34</td><td style="text-align: center;border-right: 1px solid black;background-color: #FFFF00;;">46</td><td style="text-align: center;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;border-right: 1px solid black;background-color: #C0C0C0;;">3s limits =</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">31</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF00;;">49</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;border-right: 1px solid black;background-color: #C0C0C0;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">1 > 3s or >-3s</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="border-top: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">     1 side of µ</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">between +1s and +3s</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">between +2s and +3s</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: center;border-right: 1px solid black;background-color: #C0C0C0;;">1 Signal every</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">384.6</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #C0C0C0;;">High Side</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">9.2</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">High Side</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">384.6</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">High Side</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">2500.0</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0.26%</td><td style="text-align: center;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #C0C0C0;;">Probabilities</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">10.82%</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Probabilities</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0.26%</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Probabilities</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">0.04%</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">5,000 Normal Samples</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">13</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">-</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">+</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">541</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">+</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">13</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">+</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;border-top: 1px solid black;;">37.340</td><td style="border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: center;;">35.396</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;border-right: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;;">35.996</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;border-right: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: center;;">40.809</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;border-right: 1px solid black;;">1</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;;">39.753</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;border-right: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: center;;">35.400</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;border-right: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: center;;">36.183</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;border-right: 1px solid black;;">0</td><td style="border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"> </td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"> </td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"> </td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: center;;">37.497</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;border-right: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: center;;">35.878</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;border-right: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: center;;">44.224</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;border-top: 1px solid black;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: center;;">42.136</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="text-align: center;;">36.753</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: center;;">47.436</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: center;;">39.042</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: center;;">36.210</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style="text-align: center;;">38.351</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style="text-align: center;;">42.047</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style="text-align: center;;">41.391</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style="text-align: center;;">35.217</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style="text-align: center;;">39.930</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">58</td><td style="text-align: center;;">42.151</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">59</td><td style="text-align: center;;">40.071</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">60</td><td style="text-align: center;;">37.079</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">61</td><td style="text-align: center;;">42.247</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">62</td><td style="text-align: center;;">37.400</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">63</td><td style="text-align: center;;">45.036</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">64</td><td style="text-align: center;;">40.812</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">65</td><td style="text-align: center;;">41.450</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">66</td><td style="text-align: center;;">41.087</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">67</td><td style="text-align: center;;">40.601</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">68</td><td style="text-align: center;;">38.550</td><td style=";"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">69</td><td style="text-align: center;;">40.759</td><td style=";"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Runs Rules</p><br /><br />

 

[pbornemeier]

When looking at the current and previous 7 rows in columns C or D from rows 47 to 69, the sum of values in either column C or Column D values are always 4 or greater.

For the sample shown, for any given row when C is 1, D is 0 and when C is 0, D is 1.

Why would you "reset" column E after a signal is given? If you had a series of 50 ones in column C or D wouldn't you want to see a series of ones in column E?

Does the sum have to be equal to 4 (=$F$26), or greater than or equal to 4 (=>$F$26)?

 

[rwmill9716]

Phil,

I'm trying to simulate actual process control based on runs rules. I want to show that poorly chosen runs rules will lead to many false signals. The one that I'm modeling here
is the rule where 8 or 8 have fallen to one side of the mean. I chose 4 of 8 in this example to increase the number of violations. Eight of 8 falling to one side of the mean is like flipping a coin and getting 8 heads in a row, i.e., there's a 0.5^8 (0.004 or 1 chance in 256) probability of that happening by chance.

When a process signals a violation of this rule, changes are usually made to counter this unusual event--usually the process has shifted significantly. Therefore, as soon as a signal is made, the counter must be reset since the history will be irelevant at that point.

If the runs rule is 4 of 8, then the signal should be given when 4 is reached, not =>4.

I think that my complicted "IF" statement can be simplified with an "AND" or "OR" statement.

ric

 

[pbornemeier]

This is a slightly simpler version of your formula for E47:

=IF(AND(OR(SUM(INDIRECT("D"&ROW(D47)-$H$26+1):D47)=$F$26,SUM(INDIRECT("C"&ROW(C47)-$H$26+1):C47)=$F$26), SUM(INDIRECT("E"&ROW(E46)-$F$26):E46)=0),1,0)

Why would there be a 1 in E69? SUM C62:C69 = 2 and SUM D62:D69 = 6