selecting randomly specific value in a list

[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

 

[Gary''s Student]

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

 

[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

 

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


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

 

[Nicawette]

Dear Kostis

thank you for your help,

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

 

[Nicawette]

Dear Shastri,

unfortunately the process described does not return the expected
result,

thank you for your efforts

Nico

 

[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

 

[Nicawette]

Dear Kostis,

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

Thank you & TGIF

 

[vezerid]

Glad to be of help.

Enjoy the wknd too!