Lookup or Index Match Formula

[Loss1003]

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

[p45cal]

See attached. Formula in H3 is:
=INDEX($A$2:$E$2,MATCH(RAND(),CHOOSE({1,2,3,4,5},0,A3,SUM(A3:B3),SUM(A3:C3) ,SUM(A3:D3)),1))
and copy down, but I'm sure there's a cleverer way to get a cumulative sum in a MATCH function than:
CHOOSE({1,2,3,4,5},0,A3,SUM(A3:B3),SUM(A3:C3),SUM(A3:D3))

Press F9 on the keyboard to recalculate to get new random values.
Tests with 5k calculations give percentages which seem right.

[Paul_Hossler]

I'd use a user function, but the basic logic is the same as p45cal's ws function




Option Explicit

Function ProbabilityChoice(Headers As Range, Prob As Range) As String
Dim arrProb() As Double
Dim RN As Double
Dim i As Long

Application.Volatile

ReDim arrProb(1 To Prob.Columns.Count)

arrProb(LBound(arrProb)) = Prob.Cells(1, 1).Value
For i = LBound(arrProb) + 1 To UBound(arrProb) - 1
arrProb(i) = arrProb(i - 1) + Prob.Cells(1, i).Value
Next I
arrProb(UBound(arrProb)) = 1#

Randomize
RN = Rnd
For i = LBound(arrProb) To UBound(arrProb)
If RN <= arrProb(i) Then
ProbabilityChoice = Headers.Cells(1, i).Value
Exit Function
End If
Next i
ProbabilityChoice = "oops"
End Function

[p45cal]

think the OP's lost interest.

[Loss1003]

Apologies started another thread because I forgot I posted this already. Thanks for the help. I do have one follow up question. I need to add simple formulas where the percentages are shown. However, when I add them I get a #Value message.

For example. I added a formula in B3 based on criteria in M3 and N3 and under K3 I get the #Value message in lieu of the outcome (TX or MS or NC)

[Paul_Hossler]

Your B3 formula is forcing in text values that only look like numbers


16964



Use this instead


=IF(AND(M3="hello",N3>2),0.15,0)

[Loss1003]

okay, one last adjustment. Let's say I want to copy those cells down or over to new sheet. The formula doesn't correctly work because it's forcing text values that only look like numbers. How can I adjust the formula or convert the copied cells into appropriate numbers to ensure the formula continually calculates properly

[Paul_Hossler]

It works for me but I have to make the IF() references absolute if I want to use the "hello'




=IF(AND($M$3="hello",$N$3>2),0.15,0)


I can copy down and copy to new sheet and it seems to work

Maybe I didn't understand the question

[Loss1003]

No worries, I'll try explaining a little deeper.

Basically, Column (K) is still factoring in certain cells in that row when it shouldn't be. For instance, the below rows 31, 32 the result shown is "NC". However, the cell in those rows column (C) are listed as 0.00 (no chance). Therefore they should never show up in the result column ("K") of that row.



13

15.00%

0.00%

5.88%

47.06%

47.06%

100.00%

(might result in TX, MS, NC, FL) but not NC



NC



14

15.00%

0.00%

11.76%

35.29%

52.94%

100.00%

"Ditto



NC

[Loss1003]

I kinda figured this out the selections need to equal to 1.00 (100%)

[p45cal]

the result shown is "NC". However, the cell in those rows column (C) are listed as 0.00 (no chance). Therefore they should never show up in the result column ("K") of that row.

13 15.00% 0.00% 5.88% 47.06% 47.06% 100.00% (might result in TX, MS, NC, FL) but not NC NC
14 15.00% 0.00% 11.76% 35.29% 52.94% 100.00% "Ditto NC
You realise you have 2 columns headed NC (the red above is column E headed NC), one of which always has a value >0 so you should always have the possibility of seeing NC in any row.