selecting randomly specific value in a list
 

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

selecting randomly specific value in a list
 

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

--
Gary''s Student - gsnu2007L

selecting randomly specific value in a list
 

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

On Mar 13, 12:24*pm, Nicawette wrote:
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

selecting randomly specific value in a list
 

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" wrote:

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

selecting randomly specific value in a list
 

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

selecting randomly specific value in a list
 

Dear Shastri,

unfortunately the process described does not return the expected
result,

thank you for your efforts

Nico

selecting randomly specific value in a list
 

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

On Mar 13, 6:03*pm, Nicawette wrote:
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

selecting randomly specific value in a list
 

Dear Kostis,

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

Thank you & TGIF

selecting randomly specific value in a list
 

Glad to be of help.

Enjoy the wknd too!

On Mar 13, 6:45*pm, Nicawette wrote:
Dear Kostis,

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

Thank you & TGIF