Help with monte carlo simulation

[michaelsmith559]

I have a macro that runs a monte carlo simulation that was working. I changed the values in column o and calculated new probabilities. Now when I run the macro it says the probability column must sum to 1. I have checked where I got the data from and all values and counts are correct but it will not sum to 100%, it only sums to 99%. I have even added a dummy variable to column o and added the 1% but it still says column must sum to 1. Anyhow, can someone help me correct the macro or offer a different macro to do what I want. My macro is somewhat slow. This macro uses the data analysis random number generator in a loop. Here is the code:

Sub RandomNumberTrials()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim i As Integer
For i = 1 To 184
     Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$Q$2:$Q$185"), 1, 184 _
        , 7, , ActiveSheet.Range("$O$2:$P$30")
     Application.SendKeys "{Enter}", True
    Range("U1048576").Select
    ActiveCell.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Range("Q2").End(xlDown).Value
    Next i
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Here is a screenshot:

Excel 2012
	N	O	P
1	Count	Combine	Prob
2	6	111	3%
3	15	112	8%
4	13	113	7%
5	3	114	2%
6	7	124	4%
7	7	125	4%
8	9	216	5%
9	13	217	7%
10	3	218	2%
11	10	228	5%
12	8	229	4%
13	13	3110	7%
14	2	3111	1%
15	8	319	4%
16	8	3211	4%
17	1	3212	1%
18	8	4112	4%
19	4	4113	2%
20	7	4213	4%
21	1	4214	1%
22	11	5114	6%
23	1	5214	1%
24	6	5215	3%
25	3	6115	2%
26	2	6216	1%
27	6	7116	3%
28	1	7216	1%
29	3	8116	2%
30	3	9117	2%
Predictors Pos 1

Cell Formulas
Range	Formula
N2	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O2)
N3	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O3)
N4	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O4)
N5	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O5)
N6	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O6)
N7	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O7)
N8	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O8)
N9	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O9)
N10	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O10)
N11	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O11)
N12	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O12)
N13	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O13)
N14	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O14)
N15	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O15)
N16	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O16)
N17	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O17)
N18	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O18)
N19	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O19)
N20	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O20)
N21	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O21)
N22	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O22)
N23	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O23)
N24	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O24)
N25	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O25)
N26	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O26)
N27	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O27)
N28	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O28)
N29	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O29)
N30	=COUNTIF(INDIRECT("M2:M"&COUNTA(M:M)),O30)
P2	=N2/$D$14
P3	=N3/$D$14
P4	=N4/$D$14
P5	=N5/$D$14
P6	=N6/$D$14
P7	=N7/$D$14
P8	=N8/$D$14
P9	=N9/$D$14
P10	=N10/$D$14
P11	=N11/$D$14
P12	=N12/$D$14
P13	=N13/$D$14
P14	=N14/$D$14
P15	=N15/$D$14
P16	=N16/$D$14
P17	=N17/$D$14
P18	=N18/$D$14
P19	=N19/$D$14
P20	=N20/$D$14
P21	=N21/$D$14
P22	=N22/$D$14
P23	=N23/$D$14
P24	=N24/$D$14
P25	=N25/$D$14
P26	=N26/$D$14
P27	=N27/$D$14
P28	=N28/$D$14
P29	=N29/$D$14
P30	=N30/$D$14

 

[RickXL]

Hi,

I am not a Monte Carlo Analysis expert - but I have visited the principality if that counts

However, there are a couple of things I noticed. Firstly, there is an easier way to append numbers to a list but before pursuing that, can you not use the complete list of numbers returned by the Analysis Add-In? Why do you get it to create 184 numbers then use only the last one then repeat the process 184 times?

Also, when I used your numbers, the Add-In worked without complaint and produced a list of numbers. So I decided to delve deeper and see if I could re-create what you might have started from using the formulas in your question and that is where I started getting inconsistencies.

For instance, the total of the numbers in column N is 182 not 184.

Looking at the COUNTIF formulas you count all the numbers in column M but then start the comparisons at M2 which ignores one of the numbers in the count. So even if column M has a heading that means there is a count of 183 and only 182 numbers but you are trying for 184.