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.
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
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
- oops my bad, the vlookup will give the largest rows <= to the look up value so the table needs to shift everything up 1:
8 13
150 20
280 29
...- Thank you!! This is sooo much easier.
So, I increased the Qty values by 1 to account for a Qty of 150 still falling within the 13 piece sample lot size.
8 13
151 20
281 29
501 34
1201 42
3201 50
10001 60
35001 74
150001 90
500001 102
6000000 102