CODEWITHSUNDEEP
excelrandom
ben121
ben121

Reputation: 897

RAND gives #n/a

in excel I have 

Sex   Probability     Cumulative Prob
M     80%             80%
F     20%             100%

These are in a sheet called probability of Client and run from cells E1: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

Upvotes: 0

Views: 96

Answers (2)

JLCH
JLCH

Reputation: 803

If I gather your question correctly, you simply want an randomized output of 80% M and 20% F ?

The simplest approach for that would be

=IF(RAND()>='Probability of Client'!$F$2,'Probability of Client'!$E$3,'Probability of Client'!$E$2)

Upvotes: 1

ben121
ben121

Reputation: 897

The way i added the data in the cumulative prob column was wrong I should have started with 0% then sum'd from there.

The date should have been

enter image description here

Upvotes: 0

Related Questions