RAND gives #n/a

beng986

Board Regular
Joined
Jan 10, 2012
Messages
78
hi,

in excel I have

SexProbabilityCumulative Prob
M80%80%
F20%100%

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,103
Office Version
  1. 365
Platform
  1. Windows
Your formula will work if you change G2 to 0 and G3 to 0.8
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,483
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,292
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top