Search Range using IF AND Functions - #VALUE! error

Good Morning Everyone,

I am having an issue with two Excel functions and was hoping the professionals might be able to point me in the correct direction to solve this issue.

I am creating a dimensional layout template for my work. We test component dimensions and I want to create a formal document to standardize the layout and provide some minimal statistics. I have ran into an issue with comparing a range of data values to an upper and lower spec limit.

I want to create a formula that will search my sample data and compare to the given upper and lower spec limit for the dimension being measured. I am using the IF and AND formulas to accomplish this task, however, my formula returns a #VALUE! Error. I would like the formula to return “Pass” if the data is within the upper and lower spec limit. I would like the formula to return “Fail” if any value in my sample data is outside the upper and lower spec limit.

I have double checked to ensure my cells are formatted as numerical and there are no periods, commas, or spaces in the cells I’m applying the formula to. If someone could take a look I would appreciate it.

Here is the formula I’m attempting to use:

Please Login or Register  to view this content.

I have also included an example Excel sheet for your reference.

Side note: I would like to use conditional formatting to highlight (background in red) any sample data outside the upper and lower limits. Any idea how I might do this? Conditional formatting only has a “Between” function. I would need the opposite. The use of VBA is always an option.

Thank you so much for any support you may be able to give.

Mr. Johnson,

Thank you so much for your reply. Just wondering, can this comparison be solved a number of ways or was my IF AND combination simply incorrect for this type of scenario?

I’m am unfamiliar with the SUMPRODUCT formula and I’m trying to better understand how it works in case I need to use it again in the future. After playing with the formula I believe I have an understanding now but just wondered if you or someone else could verify my thinking behind how this equation works.
When using the – syntax, does SUMPRODUCT load the values of the range into an array as either a 1, if G10>=F10 and so on through the range. Then it does the same thing for G10<=E10, again giving a value of 1 if the criteria is met and a 0 if not. Then does the SUMPRODUCT formula multiply {array1, value1} and {array2, value1}? For example, if G10>=F10 that would return a value of 1 in {array1, value1} and if G10<=E10 then that would return a value of 1 in {array2, value2}; then SUMPRODUCT(--({array1, value1}*{array2, value 1})) would return 1? Correct?

If either value did not meet the criteria then the array value would return 0, and after being multiplied against the other array the SUMPRODUCT end result would be 0?

After the SUMPRODUCT formula cycles through the arrays the end result is compared to the count value, in my case 10?

Just trying to better understand how this formula works, but I do appreciate the support. Also, thank you for your example concerning conditional formatting!

Mr. Johnson,

Thank you for clarifying that. I think I have a better understanding of how this works now.

Additionally, is there a way I could modify your conditional formatting code to not apply to blank cells? For example, if I only test 5 samples, samples 6-10 will display as non-conforming and cause a failed reading. Is there a way to disregard blank cells?

Opps NM; I used CF to remove the formatting for cells that contain blank values.

Thank you very much for your support. I will also try using my formula as an array formula.

Good Morning,

After trying Mr. Johnson’s formula above I have found it works well 99% of the time. I have been struggling to make it work the other 1% and have yet to find a solution or alternative. So here I am again to ask for help. After testing the SUMPRODUCT function, I have found there are some issues when it encounters the number zero.

As mentioned above, I am creating a form to submit test results for our lab. I would like to display whether all the measurements were in spec or not and some basic statics about the data. The issue I have ran into arises when my test data has a lower limit (LSL) of zero and a sample size of less than ten measurements.

I have attached an excel sheet to play with. As the sheet sits all items should fail and should display “Fail” in column C of the data analysis section. As you can see my LSL is set to zero, if I set to 1, everything works properly. Strange that the formula works with a LSL of zero and data for ten measurements, but there will be many instances where less than ten measurements will be taken, and in this event the calculation is incorrect.

Any thoughts or suggestions would be greatly appreciated. Thank you and Merry Christmas.

P.S. Ok unsure how to attach this excel file, here are some screen shots until I can figure out how to attach to a reply msg.

2014-12-24_9-23-31.jpg
2014-12-24_9-24-47.jpg

ahh, advance reply...

Example Dim Template(bvj).xlsm

think i may have found a solution. added an array excluding blanks to the SUMPRODUCT and now seems to be functioning. still testing tho.

Please Login or Register  to view this content.