RAND gives #n/a

[beng986]

hi,

in excel I have

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sex[/TD]
[TD]Probability[/TD]
[TD]Cumulative Prob[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]20%[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

These are in a sheet called probability of Client and run from cells E1 to G3.

In another worksheet i have put the below formula in. Most of the time the result is #n/a with the occasional 'M'. How can i choose this so either M or F is picked in line with the probability above.

=INDEX('Probability of client'!$E$2:$E$3,MATCH(RAND(),'Probability of client'!$G$2:$G$3))

thanks

 

[steve the fish]

Your formula will work if you change G2 to 0 and G3 to 0.8

 

[RoryA]

You should know the forum rules on cross-posting by now (if not, please familiarise yourself with them now) so please add links to your cross-post(s).