RANDomly select from list IF criteria is met

[Framci]

Hello,

I have been trying to come up with a formula that will randomly select names (from column B) if they have passed (in column D), generating a list (column F) of students that will be tested next.
Basically, if someone in coloumn B shows PASS in column D they can be ignored. If the users have failed or not taken the test, I would like them to be included in my random selection.
All the formulas I have tried will either give me an error, or a name that I wanted excluded...

=IF(D2="PASS", "Passed", INDEX(B2:B15, RAND()*COUNTA(B2:B15),0))

I have attached a very small sample of what I mean. I actually have thousands of names, which is making this formula business very frustrating. Hopefully someone can shed some light on my IF RAND & INDEX function...

[JohnTopley]

Try

=IF(D2="PASS","Passed",INDEX(B2:B15,RAND()*COUNTIF(D2:D15,"<>PASS")))

[jason.b75]

There isn't really an efficient way to randomise a list with formula. See if this helps.

In E2 and fill down to the last row of data, hide the column if desired.

=IF(D2<>"PASS",RAND(),"")

In F2 and filled down as required to generate the names for the test group.

=IFERROR(INDEX(B:B,MATCH(SMALL(E:E,ROWS(F$2:F2)),E:E,0)),"")

[Framci]

JohnTopley =IF(D2="PASS","Passed",INDEX(B2:B15,RAND()*COUNTIF(D2:D15,"<>PASS")))
I tried this formula in the sample data and it pulled a name that passed, as well it has several 0
I think I have screwed up the formula in the beginning with IF(D2="Pass"...
How do I say in excel -> if column D in row says "Pass", ignore name from selection ?
I think i need an array in the formula

[Framci]

jason.b75 =IF(D2<>"PASS",RAND(),"") & =IFERROR(INDEX(B:B,MATCH(SMALL(E:E,ROWS(F$2:F2)),E:E,0)),"")

I also tried this method - the amounts in column E are decimal numbers (weird?)
This did however eliminate any of those that PASS from being chosen in column F
Is there any possible way to combine these formulas?
Thank you for your help, at least I have a back up plan if I cant find a 'one step' formula

[jason.b75]

You will never do it with a single column formula, you need a separate column to generate the random values.

Each random value needs to be passed to the INDEX formula twice, one to assign the random value to the relevant row of data in the source table, then again to rank that value against the others.

Entering the RAND function twice in the same formula will not assign the same random value both times, so the current record can't even be compared to itself for ranking purposes, considering that you then have to perform the same task, which cannot be done, for each row in the table / array, you are effectively attempting impossible to the power of infinity.

Using a helper column for the random values maintains consistency for the current calculation cycle, providing the INDEX function with the comparable values it needs to effectively rank the results.

[Framci]

Thank you for the explanation jason.b75 (easy to follow, difficult to understand)

While I was testing your formula I've discovered that I should change my thinking...
Rather than ignoring anyone that PASSED, I actually need to select from only those with TBD status
The reason for this is that all FAIL will be selected for retest immediately (which I can easily filter from the list), while only a portion of the TBD will be selected at a time

Should that change the original formula in coloumn E from =IF(D2<>"PASS",RAND(),"") to the new column E formula =IF(D2="TBD",RAND(),"") ?

[jason.b75]

Yes, that is the correct change to the formula.

YOU could go one better and use

=IF(D2=$Z$99,RAND(),"")

Then you could enter TBD or FAIL in Z99 (or any suitable cell), depending on which group you want to pull names from, that way you only have to change 1 cell instead of a whole column of formula.

If you wanted to assign proportionate groups, say 20 TBD and 10 FAIL per group, then I'm sure we could find a way.

[Framci]

I just wanted to come back and let you know that this formula is working well for me - Thank you in abundance!
I had to add the extra column to my worksheet (to assign random values) - maybe if I was more willing to do this in two steps from the beginning I wouldn't have ended up so confused.


I have a really easy question for you. List of 100 names (shown in A), we want to review 10% of them (create list in B)
I was using this formula (in B)
=INDEX($A$2:$A100, ROUND(RAND()*COUNTA($A$2:$A$100),0))
But this results in duplicate names generated in column B. I currently use the Remove Duplicates tool after getting the random list of names.
Do you have a better formula for this (that would eliminate duplicates and my extra step)?

I got the formula from the internet (after struggling on my own), so I honestly don't know how effective =INDEX($A$2:$A100, ROUND(RAND()*COUNTA($A$2:$A$100),0)) actually is.

Thanks again

[JohnTopley]

You could adopt Jason's method of having a column filled using RAND() then use ..

=INDEX($A$2:$A$101,MATCH(SMALL($B$2:$B$101,ROWS($1:1)),$B$2:$B$101,0))

and drag down 10 rows

Column B (but select any column and hide if required) has =RAND() and the above formula is in column C (rows 2 to 11)