Message Boards Message Boards

1
|
10351 Views
|
4 Replies
|
7 Total Likes
View groups...
Share
Share this post:

[SOLVED] Solve for Iterative Variable, 'What If --> 'Goal Seek' in Excel

Posted 10 years ago

Hi,

How could I solve for a variable that generates another set value? I'll write a specific example with a known solution as an example.

"Solving for:" unknownVariable = ?

"Setup:"
beginningValue = 1;
endingValue = 0.03;
increments = 9;

intermediateValue1 = beginningValue * unknownVariable;
intermediateValue2 = intermediateValue1 * unknownVariable;
"..."
endingValue = intermediateValue8 * unknownVariable;

"Solution to be solved for:" unknownVariable = 0.68

Thanks,

Greg

POSTED BY: Greg
4 Replies

One could recast this in Mathematica as below.

f[1] = 1;
f[n_] := f[n - 1]*x
Solve[f[10] == .03 && x > 0, x]

(* Out[234]= {{x -> 0.677315868387}} *)

Or just take (.3)^(1/9).

POSTED BY: Daniel Lichtblau
Posted 10 years ago

That's exactly what I needed- thanks!

POSTED BY: Greg

Hello, Kind of a related question. But what if I am trying to Repeat Calculations/Iterations without a specific function?

I am trying to repeat an initial condition many times such that I get a desired final output. ONE final output depends on many subsequent calculations from ONE initial condition. But I want to get a specific final output. I have many given parameters

Clear["Global`*"]

tid = 50; taid = 20; tcid = 30; rho = 0.975; pe1 = 4.37; pe2 = 4.4509; qedom = 1021167; 

qeim = 5700; qcid = 529.59; el = 0.655; did = 4.22; dtot = 430932.474; iad = 445002; 

cad = 688723.8554;

qetot = qedom + qeim;

uel = 1 - el;

sigid = 0.20568; rid = 0.02958;

upid = N[Exp[Sqrt[1]*sigid]];

dwid = N[1/upid];

rf = N[Exp[rid*1]];

pid = N[(rf - dwid)/(upid - dwid)]; 

qid = N[1 - pid];

prf2 = 0.001*pe2*qetot-0.01*cad; 

npva = ((rho - rho^(taid + 1))/(1 - rho))*prf2 - 0.01*iad;  

I would encourage you NOT TO CHANGE the values of the parameters(you are free to change the names if you wish); these are from real data and heavily influence the results. The following parameter "cprd" is the INITIAL CONDITION (given the other parameters) that I want to change many times

exch=63.56; cprd = 200; 

pin = exch*cprd;(*63.56 is a FIXED constant and "pin" is the product exch*cprd*)

prf1[p_] = 0.001*pe1*qedom*el - 0.001*p*qcid - 0.001*pe1*qeim;

As you see, ALL THE FOLLOWING calculations depends on ONE chosen value of "pin" and if I change "cprd" all the results would change.

stsp = Table[pin*(upid^i), {i, -tid, tid}];

Dimensions[stsp] (*checking this gives a column vector*)

{101}

lensp = Length[stsp]

101

prf1[stsp];

per[i_] := prf1[stsp][[i]];

up[n1_, sig_, T_] := N[Exp[Sqrt[T/n1] sig]];

down[n1_, sig_, T_] := N[1/up[n1, sig, T]];

int[n1_, Rf_, T_] := N[Exp[Rf*(T/n1)]];

P[up_, down_, al_] := N[(al - down)/(up - down)];

Q[up_, down_, al_] := N[1 - P[up, down, al]];

mean[l_List] := Apply[Plus, l]/Length[l];

AmericanOption[p0_, n1_, sig_, T_, Rf_, exercise_Function] := 
  Module[{u = up[n1, sig, T], d = down[n1, sig, T], 
    al = int[n1, Rf, T], p, q, OpRecurse, res}, p = P[u, d, al]; 
   q = Q[u, d, al]; 
   OpRecurse[node_, level_] := 
    OpRecurse[node, level] = 
     If[level == n1, exercise[p0*d^node u^(level - node)], 
      rho*{p, q}.{OpRecurse[node, level + 1], 
          OpRecurse[node + 1, level + 1]} + 
       exercise[p0*d^node u^(level - node)]]; res = OpRecurse[0, 0]; 
   Clear[OpRecurse]; res]; 

AmericanPut[p0_, n1_, sig_, T_, Rf_] := AmericanOption[p0, n1, sig, T, 
  Rf, # &] 

AmericanPut[per[1], tcid, sigid, tcid, rid]  (*just checking*)
95832.1

tabnpvc =Table[AmericanPut[per[i], tcid, sigid, tcid, rid], {i, 1, lensp}];

e1 = 0;

newp = Table[e1, {i, lensp}, {j, lensp}];

Dimensions[newp]

{101, 101}

Table[newp[[i, Min[i + 1, lensp]]] = pid, {i, lensp}]; 


Table[newp[[i, Max[i - 1, 1]]] = qid, {i, lensp}];

    valtern = Max[#, npva] & /@ tabnpvc;

The matrix "val" is the MOST IMPORTANT. I want to REPEAT THE ITERATIONS (by changing "cprd") such that difference between the 1st and the 50th column val[[All, 1]]-val[[All, 50]] is first positive and then zero (vector contains positive numbers followed by zeros).

val = Table[e1, {i, lensp}, {j, tid + 1}];

val[[All,tid + 1]] = tabnpvc; 

Do[Table[val[[All, i]] = Max[#, npva] & /@ (prf1[stsp] + rho*newp.val[[All, i + 1]]), {i, 
    1, tid}], {tid}];

val // MatrixForm; 

Dimensions[val];

diff = val[[All, 1]] - val[[All, 50]]; (*this vector should ideally contain positive values FOLLOWED BY zeros but NOT negative*)

pos = Position[diff, _?(# < 0 &)] (*this is not desired*)
{{36}, {37}, {38}, {39}, {40}, {41}, {42}, {43}, {44}, {45}, {46},{47}}

Finally, just checking with figures

valplot[i_, j_] := {stsp[[i]], val[[All, j]][[i]]}; 

val1 = ListLinePlot[Table[valplot[i, 1], {i, 1, lensp}], 
  PlotRange -> All]; 

val2 = ListLinePlot[Table[valplot[i, 50], {i, 1, lensp}], 
  PlotRange -> All]; 

Show[val1, val2]; (*this should be such that the curves don't ever cross but converge to the value of "npva"*)

So MY DESIRED FINAL OUTPUT is that the vector "diff" should contain BOTH positive numbers and zeros. It should first be positive and then zeros (maybe "pos" should equal the null vector {}). As you see, you can keep changing the value of "cprd" until you get the desired result

I have no idea on how to do incorporate all this in a "While" or "Do" function. Please help. Thank You.

I don't know much about Excel, but the simplest type of thing would be to use Solve or Reduce e.g.

Solve[ 24*x==96, x]

Note some differences in syntax, especially use "==" for Equal as "=" is reserved for Set. Note that you can use natural language inputs, e.g. in Wolfram Alpha, if you find this easier.

Wolfram Language is pretty powerful in solving equations, and on the other hand there are more choices for how to solve something. Sometimes there is no answer (like a real x with x^2==-1) and you can then use minimization to get the closest possibility. Since solving equations can become arbitrarily hard I would guess that solving is better done in Wolfram Language than in Excel.

The idea of searching for a value is an important one in Wolfram's way of doing things. For example, find the smallest elementary cellular automaton that displays random behavior from a single black cell? Sounds hard. You don't need to understand what this is, just make a table of graphics and pick out the answer.

Table[ArrayPlot[CellularAutomaton[rule, {{1},0},{100,All}],PlotLabel->rule],{rule,0,255}]

(See Stephen Wolfram's A New Kind of Science )

POSTED BY: Todd Rowland
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract