+ Reply to Thread
Results 1 to 13 of 13

Analyzing Large Data Table to Generate a Pareto Table Using Array Function

  1. #1
    Registered User
    Join Date
    03-25-2019
    Location
    Virginia, USA
    MS-Off Ver
    Office 365
    Posts
    6

    Exclamation Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    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

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    So, obviously sample files a better to work with (hence lack of response thus far) -- but, if I've understood, perhaps something like the below would work for you?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the above, based on your samples, would generate Fail for PT01/SN0001 and NG for both SN0002 & 3.

    edit: in fact, I'm not sure that would work in more varied scenario... will review, and revert.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    above would generate # key such that result would be "NG" where key either < 1000 or MOD(key,1000) = 0 ... trying to work out how best to avoid double evaluation of key
    Last edited by XLent; 03-25-2019 at 02:40 PM.

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    OK; so the below is bloated but should work in different scenarios; similar principle to that outlined in edit - i.e. uses a number key such that:

    - integer of 0 is "ok" {all values pass upper check, decimal / lower level check redundant}
    - mod of 0 is "ok" {all values pass lower check - this is a secondary valid test should integer value exceed 0}

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by XLent; 03-25-2019 at 03:29 PM. Reason: reworded narrative

  4. #4
    Registered User
    Join Date
    03-25-2019
    Location
    Virginia, USA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    The last formula generated all "Fails" for PT01. It should be OK, NG, NG.

    I have created a sample file to work with.

    Thank you for your help.
    Attached Files Attached Files

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    apologies, I was testing and I think I posted some rogue (test) references -- the first cell on the dashboard should read:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I also swapped out "Fail" for "OK" -- I missed that point re: 5b

  6. #6
    Registered User
    Join Date
    03-25-2019
    Location
    Virginia, USA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    Hmm so the testing failed.

    First off, I made a couple small tweaks. Due to this being in a table, I modified some of the absolute references so that they would move line by line with the table. I also extended the range out the HE in preparation of finalizing the file.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I put a value of 1 in the first cell of the Database for SN0001. Database!N11, and it did not change the value to NG.

    Second, I changed just the first cell of the SN0002 to 0 (Database!N12) and it changed the NG to a Pass. So it seems that the array is not being checked properly and the OK/NG is being determined off of the first value of the array.

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    Quote Originally Posted by BotFV4
    I modified some of the absolute references so that they would move line by line with the table.
    they would have worked as provided however I confess I'd assumed the S / N positions would not necessarily align hence the INDEX / MATCH
    if, as implied, the positions will always correlate across the tables then the INDEX / MATCHes become redundant altogether.

    Quote Originally Posted by BotFV4
    I put a value of 1 in the first cell of the Database for SN0001. Database!N11, and it did not change the value to NG.
    Second, I changed just the first cell of the SN0002 to 0 (Database!N12) and it changed the NG to a Pass.
    So it seems that the array is not being checked properly and the OK/NG is being determined off of the first value of the array.
    Well, the logic / equality test in the suggested formula was based on below (from post#1)
    Quote Originally Posted by BotFV4
    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)
    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)
    per your latest test I suspect you meant greater than or equal to ?
    Assuming the above to correct then a simple update of the equality operators should suffice?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Hopefully that generates your expected results?

    note: I had to embed some spacing into the formula to get the board to allow the post... around the less than, greater than and addition operators.
    Attached Files Attached Files
    Last edited by XLent; 03-26-2019 at 12:41 PM. Reason: added attachment for OP

  8. #8
    Registered User
    Join Date
    03-25-2019
    Location
    Virginia, USA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    Quote Originally Posted by XLent View Post
    they would have worked as provided however I confess I'd assumed the S / N positions would not necessarily align hence the INDEX / MATCH
    if, as implied, the positions will always correlate across the tables then the INDEX / MATCHes become redundant altogether.


    Well, the logic / equality test in the suggested formula was based on below (from post#1)


    per your latest test I suspect you meant greater than or equal to ?
    Assuming the above to correct then a simple update of the equality operators should suffice?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Hopefully that generates your expected results?

    note: I had to embed some spacing into the formula to get the board to allow the post... around the less than, greater than and addition operators.
    So I don't wanna mess with the quote too much... but in order...

    1. The index match would be redundant for the Serial number. I took advantage of the table properties so the SN is read from the Database table starting at Row 11 and moving down. The Pass/Fail test could take advantage of the same thing.

    2. It does just need to be greater than or less than. I have the values for SN0001 all set to 0 and the upper and lower limit are also set to 0. So it would generate a OK when tested against the limits, but any value other than 0 would generate a NG.

    This updated formula generated all NGs. Does it need a small tweak to just be greater than and less than? It looks like it should do what I need bit is still failing SN0001.
    Last edited by BotFV4; 03-26-2019 at 02:11 PM.

  9. #9
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    I'll post a link on another thread to get attention of others because I'm obviously failing to grasp the requirement.

    If, as you say, the equality tests are reverted from my prior attachment / post back to their original <= and >= then yes, for SN0001, all entries would be "OK", initially

    However, I'm afraid, I then got lost with the below...

    Quote Originally Posted by BotFV4
    I put a value of 1 in the first cell of the Database for SN0001. Database!N11, and it did not change the value to NG.
    i.e. why should this change to NG given the remainder of the SN0001 / PT001 values remain 0, and thus do not pass upper boundary check (must be greater than).

    Similarly,

    Quote Originally Posted by BotFV4
    Second, I changed just the first cell of the SN0002 to 0 (Database!N12) and it changed the NG to a Pass.
    in the above scenario I presumed "OK" to be correct on grounds that:

    test 1: there is now 1 SN001 / PT01 value that is <= upper check (0) so presumed fail and proceed to test2
    test 2: all SN001 / PT01 values exceed or match the lower check (0) so presumed "fail" - i.e. "OK"

    the only way I could replicate your expected results for above tests was to make 0 a valid result, i.e. <= & >= so, as I say, I think I'm missing the point and will hand over to others.

  10. #10
    Registered User
    Join Date
    03-25-2019
    Location
    Virginia, USA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    So in order for the measurement on the Pareto to be considered "OK" all of the measurement points in the database (i.e. PT01.1, PT01.2, etc.) must be within the specification. So for PT01 I set the Upper and Lower Limits to 0 and 0 (this can be seen on the Parameters Tab). This means that the only value that would pass the Pareto equation would be 0. In the instance where I changed Database!N11 to 1, the equation should register this as NG. All of the Values within the array of Database!N11:W11 must all be within the upper and lower specification. In this example file every cell must contain a 0 in order to generate a "OK" measurement.

    In practice, the upper and lower spec limits would be different. It is just simpler to test the equation with zeros across the board. I have already set the conditional formatting to the same logic. If you set any value less than 0, the cell will highlight Blue. If you set any value greater than 0, the cell will highlight Red. The problem is that this conditional formatting does not provide metrics of the failures. This is why I have created this Pareto table to capture this information. This way we get yield rates for the different measurements and focus root cause analysis of our design.

    I hope that is clear. I apologize if I have explained this in a confusing manner.

  11. #11
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    OK; so based on your last post I think / hope I may have misinterpreted to the point of over complicating things. Does the below do what you want?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    as before I've had to insert some spaces around less than, above that and addition operations, in order to get it past the board software
    Last edited by XLent; 03-27-2019 at 11:01 AM.

  12. #12
    Registered User
    Join Date
    03-25-2019
    Location
    Virginia, USA
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    Beautiful! I think this has done it. Messing around with it for a little bit and I managed to get the results I was expecting.

    I am surprised the SUMPRODUCT worked nicely with the CSE but the IF statement I found in google searches did not.

    Edit: Do the mods mark the thread as solved or do I need to do that?
    Last edited by BotFV4; 03-27-2019 at 01:51 PM.

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Analyzing Large Data Table to Generate a Pareto Table Using Array Function

    You would mark as SOLVED (see FAQ for How To etc)

    Lost me a little on the CSE as the above SUMPRODUCT, whilst processed akin to an Array (and therefore no more efficient), does not require CSE entry.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using pivot table to lookup data from large table, but would like to edit
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2017, 12:29 PM
  2. Very slow process, sorting large table to identify range to apply array formulae
    By jasonmcasey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2015, 03:40 AM
  3. Analyzing Large Amounts of Data
    By roasthawg in forum Excel General
    Replies: 4
    Last Post: 07-04-2012, 06:29 PM
  4. Pulling non-zero data from a large table array
    By Akkori in forum Excel General
    Replies: 4
    Last Post: 04-23-2009, 02:40 PM
  5. Trying to generate a table of unique values from an array...
    By djsquare in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-21-2007, 11:41 AM
  6. [SOLVED] Need a pareto of a text table
    By Mike H in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-12-2006, 11:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1