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

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

--
Gary''s Student - gsnu2007L

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

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

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

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

Dear Kostis,

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

Thank you & TGIF

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