Loss1003
08-15-2016, 11:51 AM
I need to create a formula that can randomly select (lookup) one of the values in Row 2 (TX, NC, MS, NC, or FL) based on the assigned probability (%) table in each of their respective rows starting in row 3.
Please assist in creating a formula to help achieve my outcome.
For example, in row 3 NC has a probability percentage of 77.78 of always being the value selected in that row. MS only has 22.22%, all other values have zero (0%) chance of being the outcome value.
Row 1
2
TX
NC
MS
NC
FL
TOTAL
Outcome
3
0.00%
0.00%
22.22%
77.78%
0.00%
100.00%
Should be either NC or MS
4
0.00%
0.00%
35.29%
64.71%
0.00%
100.00%
Should be either NC or MS
5
0.00%
0.00%
17.65%
76.47%
5.88%
100.00%
(NC or MS or FL)
6
0.00%
0.00%
29.41%
58.82%
11.76%
100.00%
(NC or MS or FL)
7
0.00%
0.00%
43.75%
50.00%
6.25%
100.00%
(NC or MS or FL)
8
0.00%
0.00%
35.29%
35.29%
29.41%
100.00%
(NC or MS or FL)
9
0.00%
0.00%
23.53%
64.71%
11.76%
100.00%
(NC or MS or FL)
10
0.00%
0.00%
0.00%
47.06%
52.94%
100.00%
(NC or MS or FL)
11
0.00%
0.00%
11.76%
52.94%
35.29%
100.00%
(NC or MS or FL)
12
0.00%
0.00%
35.29%
29.41%
35.29%
100.00%
(NC or MS or FL)
13
0.00%
0.00%
5.88%
47.06%
47.06%
100.00%
(NC or MS or FL)
14
0.00%
0.00%
11.76%
35.29%
52.94%
100.00%
(NC or MS or FL)
15
0.00%
0.00%
29.41%
47.06%
23.53%
100.00%
(NC or MS or FL)
16
5.00%
5.00%
19.41%
35.29%
35.29%
100.00%
(TX, NC, MS, NC or FL)
I'm decent at using vlookup formulas but wouldn't know where to start or if index/match would be a better fit. :thumb
Please assist in creating a formula to help achieve my outcome.
For example, in row 3 NC has a probability percentage of 77.78 of always being the value selected in that row. MS only has 22.22%, all other values have zero (0%) chance of being the outcome value.
Row 1
2
TX
NC
MS
NC
FL
TOTAL
Outcome
3
0.00%
0.00%
22.22%
77.78%
0.00%
100.00%
Should be either NC or MS
4
0.00%
0.00%
35.29%
64.71%
0.00%
100.00%
Should be either NC or MS
5
0.00%
0.00%
17.65%
76.47%
5.88%
100.00%
(NC or MS or FL)
6
0.00%
0.00%
29.41%
58.82%
11.76%
100.00%
(NC or MS or FL)
7
0.00%
0.00%
43.75%
50.00%
6.25%
100.00%
(NC or MS or FL)
8
0.00%
0.00%
35.29%
35.29%
29.41%
100.00%
(NC or MS or FL)
9
0.00%
0.00%
23.53%
64.71%
11.76%
100.00%
(NC or MS or FL)
10
0.00%
0.00%
0.00%
47.06%
52.94%
100.00%
(NC or MS or FL)
11
0.00%
0.00%
11.76%
52.94%
35.29%
100.00%
(NC or MS or FL)
12
0.00%
0.00%
35.29%
29.41%
35.29%
100.00%
(NC or MS or FL)
13
0.00%
0.00%
5.88%
47.06%
47.06%
100.00%
(NC or MS or FL)
14
0.00%
0.00%
11.76%
35.29%
52.94%
100.00%
(NC or MS or FL)
15
0.00%
0.00%
29.41%
47.06%
23.53%
100.00%
(NC or MS or FL)
16
5.00%
5.00%
19.41%
35.29%
35.29%
100.00%
(TX, NC, MS, NC or FL)
I'm decent at using vlookup formulas but wouldn't know where to start or if index/match would be a better fit. :thumb