# Evaluating a string of text inside a formula

#### lodidodi

##### New Member
My end goal is to create a model that predicts revenue from closing fees based on the amount of deals done every month, after applying a failure rate.

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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### sericom

##### Well-known Member
You could add the following module:

Function Eval(str As String)
Application.Volatile
Eval = Evaluate(str)
End Function

=REPT("IF(RAND()>K2,1,0)+",D5)

And to evaluate it use the following

=Eval(LEFT(D6,LEN(D6)-1))

That assumes your formula is in D6.

#### lodidodi

##### New Member
The problem I found with using the evaluate formula is that it won't update when a user changes the value of an input cell. They have to manually recalculate the formula which isn't ideal for a financial model.

Replies
2
Views
232
Replies
4
Views
1K
Replies
3
Views
185
Replies
6
Views
96
Replies
3
Views
301

1,137,353
Messages
5,680,998
Members
419,948
Latest member
Sbakker1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back