Excel Randbetween in excel puzzle

Question

Here is my problem.

If cell A1 is not empty then I would like to choose a random name from a list (the names are Ben, Bill, Bob). The formula for this as follows: IF(A1<>"",INDEX(Q:Q,RANDBETWEEN(2,COUNTA(Q:Q))))

So far so good...

However, say A1 = Ben, Bill or Bob, then I would like my formula to exclude that name and pick another name from the list.

I can't figure out how to do this annoyingly. Any help is much appreciated, thanks.

Answer 1

EDIT P.b in the comments notes that my filter can be reduced from

FILTER(FILTER(F:F,F:F<>""),FILTER(F:F,F:F<>"")<>A1) 

to

FILTER(F:F,(F:F<>"")*(F:F<>A1)) 

making the final function:

=INDEX(
  FILTER(F:F,(F:F<>"")*(F:F<>A1)),
  RANDBETWEEN(1, COUNTA(FILTER(F:F,(F:F<>"")*(F:F<>A1))))
)

Also, if you have FILTER you probably have access to LET, so you can define the list of names and reuse it, like this:

=LET(
  names, FILTER(F:F,(F:F<>"")*(F:F<>A1)),
  INDEX(names, RANDBETWEEN(1, COUNTA(names))
))

Which is a lot more readable. LET takes pairs of variables and definitions and a final formula, so it's LET(varName, reference, formula), which can make complex formulas much easier to work with. I believe LET can take 126 name/reference pairs before the formula.

Original solution below:

---

If you are using a version of Excel that has FILTER, you can use it to filter the list of names, obtain the count, then pass that filtered list to INDEX along with a RANDBETWEEN using that count. Assuming your list of names is in Col F:

=INDEX(
  FILTER(FILTER(F:F, F:F<>""), FILTER(F:F, F:F<>"")<>A1),
  RANDBETWEEN(1, COUNTA(FILTER(FILTER(F:F, F:F<>""), FILTER(F:F, F:F<>"")<>A1)))
)

I am passing F to FILTER twice, once to remove empty rows and once to remove whatever name is in A1. This is the basic filtered list:

FILTER(FILTER(F:F,F:F<>""),FILTER(F:F,F:F<>"")<>A1)

As you can see, I pass that same filtered list to COUNTA and use that in RANDBETWEEN to get an INDEX from the list.

Here is a version using FILTERXML to filter the list. This should be supported in Windows versions back to 2012:

=INDEX(
  FILTERXML("<a><x>" & TEXTJOIN("</x><x>", TRUE, IF((F:F<>A1)*(F:F<>"")>0, F:F, "")) & "</x></a>", "//x"),
  RANDBETWEEN(1, SUM((F:F<>A1)*(F:F<>"")))
)

Here, we are going to use array products to generate a list of matching conditions. We can use the array product passed to sum to get the count for RANDBETWEEN, and also use it to filter the name list, then pass that list to TEXTJOIN to convert it into an xml string, then use FILTERXML to parse the node values back out into an array.

F:F=A1 will return a true/false array, and (F:F=A1)*(F:F<>"") will return a true/false array where F matches both conditions. And we can pass this to SUM to get a count:

=SUM((F:F<>A1)*(F:F<>""))

We can also pass this array to IF and get a list of matching names. We'll tell IF to return the values from F if TRUE or "" if FALSE.

IF((F:F<>A1)*(F:F<>"")>0, F:F, ""))

or to TEXTJOIN, telling it to ignore the empty values, using the separator to generate an xml string:

="<a><x>" & TEXTJOIN("</x><x>", TRUE, IF((F:F<>A1)*(F:F<>"")>0, F:F, "")) & "</x></a>"

And then use FILTERXML with //x to retrieve the values of the x nodes as an array. Then we pass that result to INDEX, along with RANDBETWEEN where we use the sum formula from before as the upper bound. A little hackier than the first one, but it should still work.

Answer 2

Using dynamic array functionality in ms 365, try:

Formula in B1:

=@SORTBY(FILTER(D1:D3,D1:D3<>A1),RANDARRAY(2))

---

Note1: RANDARRAY() can be a bit more dynamic: RANDARRAY(COUNTA(D1:D3)-1).

Note2: With LET() it can be written as =@LET(A,D1:D3,SORTBY(FILTER(A,A<>A1),RANDARRAY(COUNTA(A)-1)))

Answer 3

Option for older versions:

=IF(A1<>"",INDEX(Q:Q,AGGREGATE(15,6,ROW(Q:Q)/((Q:Q<>A1)*(Q:Q<>"")),RANDBETWEEN(1,SUMPRODUCT((Q:Q<>"")*(Q:Q<>A1))))))