For example, in month 4, I have 4 deals that will be started. Based upon a close rate of 70%, I need to predict how many will be closed.

I am doing this using the rand() formula to create a simple monte carlo analysis.

Essentially, if rand()<70%, the deal closes. The 70% is a changeable variable stored in cell K2, so my base formula is “IF(RAND()>K2,1,0)”.

The problem is that this only works when there is one deal started each month. I need to model the ability to have multiple deals started each month. To accommodate this, I have created the formula “=REPT("IF(RAND()>K2,1,0),",D5)”, assuming that D5 is the monthly deals started.

So, if D5 is 4, the result is IF(RAND()>K2,1,0),IF(RAND()>K2,1,0),IF(RAND()>K2,1,0),IF(RAND()>K2,1,0),.

My goal is to then insert this into a sum() formula to calculate the result but this is where I am stuck. I cannot get the sum() formula to evaluate this text. I tried using indirect() in a variety of ways but am having no luck.

Can anyone help?

If you have another way to model this, I would open to that as well.