I have been working with a complex array functions to try to generate a Pareto table. I have been hitting the wall hard for a while. I figured I would have an intro post with a doozy.
Here is what I am looking to do...
Pareto Formula
1. Read the array of column headers in the Database Table (N3:HE3)
2. Create a header sub array based on a match of the column header in the Pareto Table (i.e. PT01 values are from N3:W3).
Note: The Database column headers have to be trimmed using a Left function. There are multiple measurements of a specific measurement type. PT01 might have 8 measurements in on project, but 10 in another.
3. The header sub array lines up with a data sub array of values directly below the Database column headers starting in Row 11.
So if I am looking at PT01 in columns N3:W3, I want to test the values in N11:W11. I am currently not making the row reference absolute the Pareto Table updates the formula for each row.
4. Test each value in the Data sub array if it is greater than the Upper Spec Limit in the given column of the Database (row 7)
a. If pass, NG
b. If fail, go to 4
5. Test each value in the Data sub array if it is less than the Lower Spec Limit in the given column of the Database (row 8)
a. If pass, NG
b. If fail, OK
Couple quick notes...
I believe the main issue is that the Index is not providing an Array of values to test. I have tried using CSE IF statements but it only test the first value of the array.
The Spec Limits automatically update based on a Parameters Table. I have tried to test the data against the Parameters Table but that just seems to add unnecessary length and complexity to the formula. I feel it would be easier to test it against the values within the column. The only reason they are all 0s currently is because I have the template cleared out.
I have implemented procedures so that the Measurement Point types are always going to be 4 characters for the match.
The values being tested will always be in a continuous array of columns.
The Database Table and Pareto Table have named ranges. I have been using the cell references for simplicity. I am not certain if named ranges cause issues with the array function.
Pareto.JPG
Data.JPG
Bookmarks