Forum Discussion
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 a...
- 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.
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.
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.
First off, thank you so much for your response! Much appreciated.
1) My interpretation does seem to be incorrect. I'm new to the machining world at this company and it appears that the Frequency of "1 in X" is based more on a sequential sampling rather than a straight QC C=0 methodology.
2) That vlookup is so much easier than nesting all the IF statements.
3) Looks like an additional Row would be required to indicate the Overall Sample Lot Size.
1) My interpretation does seem to be incorrect. I'm new to the machining world at this company and it appears that the Frequency of "1 in X" is based more on a sequential sampling rather than a straight QC C=0 methodology.
2) That vlookup is so much easier than nesting all the IF statements.
3) Looks like an additional Row would be required to indicate the Overall Sample Lot Size.