selecting randomly specific value in a list

  • Thread starter Thread starter Nicawette
  • Start date Start date
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
 
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
 
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.


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
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
 
Dear Shastri,

unfortunately the process described does not return the expected
result,

thank you for your efforts

Nico
 
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
 
Back
Top