Forum Discussion

inspect85's avatar
inspect85
Copper Contributor
Dec 07, 2021
Solved

Advanced Autofill Excel Function

Hello,

I'm reaching out to see if this is a possibility within the Excel function limits.

 

I'm looking for a way to input a specific range of number (quantity of lot sizes) and have it autofill another cell with "1 In 13" etc. 

 

So, the end result would look like this

(Range Input) "Qty: 2 to 8" would populate "100%" 

(Range Input) "Qty: 9 to 150" would populate "1 in 13" 

(Range Input) "Qty: 151 to 280" would populate "1 in 20" 

(Range Input) "Qty: 281to 500" would populate "1 in 29" 

(Range Input) "Qty: 501 to 1200" would populate "1 in 34"

(Range Input) "Qty: 1201 to 3200" would populate"1 in 42"

(Range Input) "Qty: 3201 to 10,000" would populate "1 in 50"

(Range Input) "Qty: 10,001 to 35,000" would populate "1 in 60"

(Range Input) "Qty: 35,001 to 150,000" would populate "1 in 74"

(Range Input) "Qty: 150,001 to 500,000" would populate"1 in 90"

(Range Input) "Qty: 500,001 and over" would populate "1 in 102"

 

  • mtarler's avatar
    mtarler
    Dec 09, 2021
    First can I ask where that table comes from? I have use many sampling tables and concerned about your intent. Basically it sounds like you are reading the table that if you get 26 units in (i.e. between 8 and 150) that you only need to inspect 2 units (as in "1 IN 13" would be 2 for 26 units) but I'm pretty sure that is incorrect reading of this table and 13 IS the number of units that need to be inspected.
    As for your excel question (despite my concern about it being correct) I have 2 comments:
    a) I suggest you use IFS() instead of nested IF() statements if you go that route
    b) BETTER yet, I suggest you put somewhere a small table of the values and then use a LOOKUP function (VLOOKUP or XLOOKUP) to find the corresponding value. So the table would look something like:
    8 100%
    150 13
    280 20
    ...

    and then the formula would be something like
    =IF(BK5<=8,"100%","1 IN " & VLOOKUP(BK5, TableOfValues, 2, true) )
    note I still use the IF and check for the 8 because that has a different format. If you were OK with it saying "1 IN 1" (which is technically the same as 100% but looks weird) then you could skip the IF statement all together.

8 Replies

  • inspect85's avatar
    inspect85
    Copper Contributor
    So, I realized I made it more complicated than it actually should be.
    Didn't realize that the IF function was sufficient enough to accomplish what was needed.
    It just ended up being =IF(BK5<=8,"100%",IF(BK5<=150,"1 IN 13")) so on and so forth.
    • mtarler's avatar
      mtarler
      Silver Contributor
      First can I ask where that table comes from? I have use many sampling tables and concerned about your intent. Basically it sounds like you are reading the table that if you get 26 units in (i.e. between 8 and 150) that you only need to inspect 2 units (as in "1 IN 13" would be 2 for 26 units) but I'm pretty sure that is incorrect reading of this table and 13 IS the number of units that need to be inspected.
      As for your excel question (despite my concern about it being correct) I have 2 comments:
      a) I suggest you use IFS() instead of nested IF() statements if you go that route
      b) BETTER yet, I suggest you put somewhere a small table of the values and then use a LOOKUP function (VLOOKUP or XLOOKUP) to find the corresponding value. So the table would look something like:
      8 100%
      150 13
      280 20
      ...

      and then the formula would be something like
      =IF(BK5<=8,"100%","1 IN " & VLOOKUP(BK5, TableOfValues, 2, true) )
      note I still use the IF and check for the 8 because that has a different format. If you were OK with it saying "1 IN 1" (which is technically the same as 100% but looks weird) then you could skip the IF statement all together.
      • inspect85's avatar
        inspect85
        Copper Contributor
        How would the IF statement look without the "1 IN" words? Does another IF statement need to go in front of the VLOOKUP to get the higher output values?

        I entered in =IF(BK5<=8,"100%",(VLOOKUP(BK5,BS10:BT20,2,TRUE))) but am getting back an output of just "1" when going above the number '8'.

        Table of values is this
        8 100%
        150 13
        280 20
        500 29
        1200 34
        3200 42
        10000 50
        35000 60
        150000 74
        500000 90
        5000000 102


    • inspect85's avatar
      inspect85
      Copper Contributor

      Juliano-Petrukio - So, the sample data comes from the c=0 sampling plan for lot inspection based on an AQL of 1.0. The populated "1 in 13, etc" data provided below would be auto-generated based on a lot size qty entered in by the user. 

       

       

Resources