# selecting randomly specific value in a list

N

#### Nicawette

Hi all,

I have a list of data with 8 records, these records can be either N/A
or the name of a user.

Column A
User1
User2
User3
N/A
User5
N/A
N/A
User8

I would like to choose at random only the name of one of the user and
not selecting the N/A, would it be possible with an excel formula?

Thank you

Nico

G

#### Gary''s Student

=OFFSET(\$A\$1,CHOOSE(RANDBETWEEN(1,5),1,2,3,5,8)-1,0)

V

#### vezerid

Say your data are in A2:A9. Use the following *array* formula:

=INDEX(A2:A9,LARGE(IF(1-ISERROR(A2:A9),ROW(A2:A9)-ROW(A2)+1),1+INT(RAND
()*SUMPRODUCT(1-ISERROR(A2:A9)))))

As it is an array formula you need to commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides

H

#### HARSHAWARDHAN. S .SHASTRI

Hi Nicawette,

Put following formula in cell b2 and drag it up to end.

=IF(a2="N/A","",COUNTA(\$A\$2:A2)-COUNTIF(\$A\$2:A2,"N/A"))

Now put following formula say in cell C1

=INT(RAND()*(MAX(B2:B100)-1)+2)

Put following formula in say cell C2

=INDEX(A2:A100,C1)

H S Shastri

If useful pl press YES.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

N

#### Nicawette

Dear Kostis

The problem is that the N/A value that I have in my list is not the
"real" #N/A error of excel, thus the iserror function can not be
applied.

Nicolas

N

#### Nicawette

Dear Shastri,

unfortunately the process described does not return the expected
result,

Nico

V

#### vezerid

If there is a particular string that you want to avoid, e.g.
"myvalue", then use the following variant (again an array formula)

=INDEX(A2:A9,LARGE(IF(A2:A9<>"myvalue",ROW(A2:A9)-ROW(A2)+1),1+INT(RAND
()*SUMPRODUCT(--(A2:A9<>"myvalue")))))

HTH
Kostis

N

#### Nicawette

Dear Kostis,

GR8, It is exactly what I want, thank you for the tip

Thank you & TGIF

V