Forum Discussion
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"
- 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
- 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.- 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.- 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
- Can you share some sample data?
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.
