random on a list only for specific value.

[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 for your help

Nico

 

[Shane Devenshire]

Hi,

Suppose your data is in A1:A10, in B1 enter the formula

=IF(A1<>"N/A",RAND())

Copy this formula down. Sort Ascending and pick the first entry.

 

[Nicawette]

Dear Shane,

Thank you but it is not exactly what I want :-(

I willl try to explain better

I have this formula

=INDEX(LIST,finduser(G1,Countusers,RANDBETWEEN(1,Countusers)),1)

where
LIST refers to the list of users (as mentioned in the previous post)

Finduser is a VB function
----------------------------
Function Finduser(myno, totno, inc) As Integer
Dim res As Integer

res = myno + inc ' Get new indes
If res > totno Then ' Roll round if too big
res = res - totno
End If

If res = myno Then ' Cannot be me, so add 1
res = res + 1
If res > totno Then ' Scroll round again if too big
res = res - totno
End If
End If

FindBank = res
End Function
-----------------------------
Countusers count the number of users (different than "N/A")
G1 refers to the current user of the spreadsheet

So this formula returns me a random user from a list that is not the
current user of the spreadsheet but it also returns me the value N/A
mentioned in the list of users.

How can I avoid the value N/A?

Thank you again for your help

Nico