Find Whether Test Results Fall within Their Limit in Google Sheets

If you’re wondering how to find whether test results fall within their limit in Google Sheets, you’re in luck! There are a couple of options you can use to accomplish this task. Let’s explore both methods together.

Using the ISBETWEEN Formula (Solution 1)

One way to determine if test results fall within their limits is by using the ISBETWEEN formula. Consider the table below as an example:

In this table, column B contains the test results (array 1), while columns C and D represent the start and end limits (array 2 and array 3) respectively. We’ll use these arrays in our formula.

To use the ISBETWEEN formula, simply enter the following formula in cell F2:

=isbetween(B2:B5,C2:C5,D2:D5)

This formula compares the values in array 1 with the corresponding values in arrays 2 and 3. The result will be displayed in the range F2:F5.

Alternatively, if your table contains blank rows, you can modify the formula to only expand to rows that have values in the “Test Result” column. Use the following formula in cell F2:

=ArrayFormula(if(len(B2:B),isbetween(B2:B,C2:C,D2:D),))

This formula includes additional Google Sheets functions like ARRAYFORMULA, IF, and LEN to achieve the desired result.

Using Operator-Based Formula (Solution 2)

Another way to compare test results with their limits in Google Sheets is by using comparison operators. This method is quite similar to the ISBETWEEN formula we discussed earlier.

Enter the following formula in cell F2:

=ArrayFormula(if((B2:B5>=C2:C5)*(B2:B5<=D2:D5),TRUE,FALSE))

Like the ISBETWEEN formula, this formula compares the values in array 1 with arrays 2 and 3. The result will be displayed in the range F2:F5.

Just like before, if you have blank rows in your table, you can modify the formula to limit the range reference. Use this formula in cell F2:

=ArrayFormula(IF(LEN(B2:B),if((B2:B>=C2:C)*(B2:B<=D2:D),TRUE,FALSE),))

Comparison of Outputs

You can choose either of the above formulas to test whether results fall within their limits in Google Sheets. However, I prefer using the ISBETWEEN formula because it is easier to learn.

There is an additional reason for my preference. When conducting this test, we usually expect the values in the “Range Upper Limit” to be greater than or equal to the “Range Lower Limit.” The ISBETWEEN function can identify any inconsistencies in this regard. If the values don’t meet this expectation, the ISBETWEEN formula will return a #NUM error.

Highlight Test Results That Do Not Fall within Their Limits

Instead of highlighting cells, let’s highlight the text/values themselves. Here’s how you can do it:

1. Go to cell B2 and make it the active cell.
2. Navigate to the “Format” menu and click on “Conditional formatting.”
3. Replace B2 in the “Apply to range” field with B2:B1000 (adjust the range as needed).
4. Under “Format values,” select “Custom formula is” from the drop-down menu.
5. Paste the formula =isbetween(B2,C2,D2)=false in the provided field.
6. Below that, you will find “Formatting styles.” Change the text color to red, make it bold, and set the fill color to none.
7. Click “Done.”

By following these steps, you can easily highlight test results that don’t fall within their limits in Google Sheets.

And there you have it! These are the different options available to test whether results fall within their set limits in Google Sheets. If you have any further questions, don’t hesitate to reach out. Happy testing!

Learn more about Google Sheets and other useful tips and tricks at Crawlan.com.