Evaluating a string of text inside a formula

lodidodi

New Member
Joined
Feb 10, 2015
Messages
2
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
Joined
Jan 19, 2006
Messages
915
You could add the following module:

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

Then change your formula to
=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
Joined
Feb 10, 2015
Messages
2
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top