Advanced Autofill Excel Function

[inspect85]

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"

[#2]

Can you share some sample data?

[#3]

@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. 

[#4]

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.

[#5 · Accepted solution]

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.

[#6]

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.

[#7]

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

[#8]

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
...

[#9]

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