Sample results in Excel: which result is outside the limits you typed
A sample result log is one row per sample. You type the sample, the method word, the low limit, the high limit, and the result. The window says in when the result is between the two limits, including a result that equals either limit. It says out when the result is below the low limit or above the high limit. It says check when the low limit you typed is above the high limit. It stays blank when the row is closed or any of the three numbers is empty. The first screen is a sheet named Samples. Row 1 is the header. Row 2 is one sample you can check by hand: low 60, high 80, result 72, status open. The window is in. There is no defect count and no chart on that screen. This sheet is not a defect rate. That rate is defects divided by the quantity made. This list uses one result against two limits you typed. It is not a control chart, which draws a mean and limits from a series. It is not a meter reading, which subtracts two indexes. The method is a word you type so you can filter. You type the result. The question is which open row is outside the limits. Closed rows drop out of the window. They stay on the sheet. A result equal to 60 against a low of 60 is in, not out. Elyx can write the window. You still type the limits and the result. The trial lasts 7 days, with no card.
The Problem
The result fails when it lives in a lab book. A sentence says sample S2 was high. Nobody can count the outs, because the two limits and the result are not on the same row. A sentence without those three numbers is not a result log. A second failure is mixing this sheet with a defect rate, a control chart, or a meter log. A defect row answers defects divided by quantity made. A control chart answers a mean and limits calculated from a run of points. A meter row answers a current index minus a previous index. If you paste defect counts, chart points, or indexes here, the morning count no longer means results outside the limits you typed. Keep this sheet to samples and the three numbers. A third failure is deleting a sample when you have read it. Set the status to closed and leave the row. The window goes blank. Deleting the row removes the only record of the 60, the 80, and the 72 you already typed. The sheet still does not decide what the limits should have been.
Benefits
Each sample is one row with a low limit, a high limit, and a result you typed.
In means the result is between the limits, including a result that equals a limit.
Out means the result is below the low limit or above the high limit.
Check means the low limit is above the high limit, so the band was typed backwards.
Closed rows stay on the sheet and drop out of the out count.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Samples. In row 1 type: Sample, Method, Low, High, Result, Status, Window, Note. Sample is the identifier. Method is a word you type. Low, High, and Result are numbers you typed, not a formula. Window is a formula. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells.
One row per sample. A second result on the same sample is a second row. Do not pack two results into one cell.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open and closed. Open means the result is still in the morning count. Closed means you are done with that sample. Only open stays in the window. People must pick a word. A synonym such as done or read will not match the formulas.
Low, High, and Result are numbers. Do not type the word out in those columns.
Enter five samples you can check by hand
On row 2 type sample S1, method pH, low 60, high 80, result 72, status open. You will predict window in. On row 3 type S2, low 60, high 80, result 85, status open. Window should be out. On row 4 type S3, low 60, high 80, result 60, status open. Window should be in, because equal to the low limit is inside. On row 5 type S4, low 80, high 60, result 70, status open. Window should be check, because 80 is above 60. On row 6 type S5, low 60, high 80, result 91, status closed. Window should go blank. Use these numbers before you trust the formula.
If you do not know the result yet, leave Result blank. The window stays blank. Do not type zero unless the result really is zero.
Write the window
The window is blank when status is closed, or when Low, High, or Result is blank. If Low is above High, the window is check. Otherwise it is out when Result is below Low or above High, and in when Result is between them, including the two limits. With Low in C2, High in D2, Result in E2, and Status in F2, use =IF(OR(F2="closed",C2="",D2="",E2=""),"",IF(C2>D2,"check",IF(OR(E2<C2,E2>D2),"out","in"))). The method word is not in this formula. The sheet does not compute a mean.
=IF(OR(F2="closed",C2="",D2="",E2=""),"",IF(C2>D2,"check",IF(OR(E2<C2,E2>D2),"out","in")))72 against 60 and 80 is in. 85 against 60 and 80 is out. 60 against a low of 60 is in.
Count in, out, and check
On a sheet named Morning, the out rows are =COUNTIFS(Samples!G:G,"out"). With the five rows above, that count is 1: sample S2, result 85. The in count is =COUNTIFS(Samples!G:G,"in"), which is 2: S1 at 72 and S3 at 60. The check count is =COUNTIFS(Samples!G:G,"check"), which is 1: S4, because the low of 80 is above the high of 60. Closed S5 is out of every count because its window is blank. A blank window is not out. Write the rule next to the count.
=COUNTIFS(Samples!G:G,"out")Do not add check rows into the out count. Check is a different word, so COUNTIFS leaves them out.
Read a reversed band as check, not as out
If Low is 80 and High is 60, the band was typed backwards. The window says check even when the result 70 would have sat between 60 and 80. Fix the two limit cells. Do not delete the row. A window on a closed row should not happen, because closed blanks the cell. If you see a word on a closed row, the formula was overwritten. S5 would have been out, because 91 is above 80, but closed keeps it off the morning list.
Filter Window to out for the morning list. Filter Method to pH when you only want that word. Filter Status to closed when you need history.
Leave the limits as numbers you typed
The formula does not average the column and it does not build a control limit from the results. If you want a different band, type a different low or high. Changing the result from 72 to 85 moves S1 from in to out. Changing the high from 80 to 90 would move S2 from out to in, because 85 would then be inside. Do that only when 90 is the limit you mean, not to hide a row.
A result pasted as text will not compare. Re-enter 85 as a number, then read the window again.
Check the five rows before you share the count
Low 60, high 80, result 72, status open: in. Result 85: out. Result 60: in. Low 80 and high 60 with result 70: check. Result 91 with status closed: blank. The out count is 1, the in count is 2, and the check count is 1. If a check fails, fix the limits, the result, or the status word before you send the count. The count is only this sheet. It is not a defect rate, and it is not a control chart.
Write the five checks on Morning if more than one person updates the samples.
Template Features
Window from the two limits
In, out, or check. Blank when status is closed or a number is empty.
=IF(OR(F2="closed",C2="",D2="",E2=""),"",IF(C2>D2,"check",IF(OR(E2<C2,E2>D2),"out","in")))Out count
COUNTIFS reads the window word. With the sample rows the count is 1.
=COUNTIFS(Samples!G:G,"out")In count
A result equal to a limit is in. The sample count is 2.
=COUNTIFS(Samples!G:G,"in")Check count
Rows whose low limit is above the high limit. The sample count is 1.
=COUNTIFS(Samples!G:G,"check")History stays on the sheet
Closed rows remain. They leave the counts because the window is blank.
Concrete Examples
72 is inside 60 and 80
Sample S1 has a result of 72. The limits you typed are 60 and 80.
Low: 60. High: 80. Result: 72. Status: open.
Result: Window is in. The out count does not include the row.
85 is outside
Sample S2 uses the same limits and a result of 85.
Low: 60. High: 80. Result: 85. Status: open.
Result: Window is out. The out count is 1.
The limits were typed backwards
Sample S4 has low 80 and high 60. The result is 70.
Low: 80. High: 60. Result: 70. Status: open.
Result: Window is check. It is not in and it is not out. Swap the two limits if 60 to 80 was what you meant.
Pro Tips
Equal to the limit is in
A result of 60 against a low of 60 is in. Out starts only when the result is strictly below the low or strictly above the high.
Method is a label
pH lets you filter. The window formula never reads the method. A second method on the same sample is another row, with its own limits and result.
Text results do not compare
If Result was pasted as text, the comparison will not mark 85 as out. Re-enter 85 as a number. Then read the window again.
Distinct from a defect rate and a control chart
A defect rate divides a defect count by a quantity made. A control chart calculates limits from a series. A meter log subtracts two indexes. Do not paste those rows here and expect this count to mean the same thing.