I am trying to solve the equation T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38),
where "L, K & S" are constants and "i" is determined from a table based on
time. "T" is time in minutes. To solve the equation fully, once "T" is
solved for it is compared with the time value used in the table that "i" was
determined from. If necessary, a new value of "i" is determined and the
equation is solved again. This is repeated until an acceptable tolerance
between "T" and the value used to determine "i" is reached. Is there an easy
way to set this up in excel to run an iterative process with a table? Any
help would be greatly appreciated.

"Chris" wrote:
I am trying to solve the equation
T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38)
[....]

Is there an easy way to set this up in excel to
run an iterative process with a table?

Take a look at Solver (Tools Solver in Excel 2003).

You do not provide enough details for me to offer specific guidance. But as
an example, I set up the following model. Assume that T, L, K, S and i are
in B1 through B5 respectively. Set up the following formulas:

B1: =(11.4*B2^0.5)*(B3^0.52)*(B4^-0.318)*(B5^-0.38)

B6: =ABS(B2-B5)

In Solver, set Target to B6 "equal to min"; set the "by changing" list to
B2, B3, B4, B5; and set appropriate constraints for each cell in B2:B5.

Without sufficient information, I set the contraints to B2=1 and similarly
for B3:B5. Solver found the following solutions (all numbers are
approximate):

B1, T: 4.74556655
B2, L: 1.005004378
B3, K: 1.200421826
B4, S: 3.325846819
B5, i: 4.745566554
B6: 3.91799E-09


"i" is determined from a table based on time.

I suspect this can be accomplished with the use of some helper cells. I did
not bother to try. However, in my experience, Solver has difficulty with
discrete (i.e. non-continuous) variables. Caveat emptor!


----- original message -----

"Chris" wrote:
I am trying to solve the equation T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38),
where "L, K & S" are constants and "i" is determined from a table based on
time. "T" is time in minutes. To solve the equation fully, once "T" is
solved for it is compared with the time value used in the table that "i" was
determined from. If necessary, a new value of "i" is determined and the
equation is solved again. This is repeated until an acceptable tolerance
between "T" and the value used to determine "i" is reached. Is there an easy
way to set this up in excel to run an iterative process with a table? Any
help would be greatly appreciated.

Hi Chris,
Excel can be used to solve explicit equation (equations where the unknown
appears on both sides)
But your equation is implicit
We can find I by rearranging you equation to give
I =((11.4*L^0.5)*(K^0.52)*(S^-0.318)/T)^(1/0.38)

But I get the impression you have a table and you what to find quantity i by
interpolation
Happy to have private email from you (this looks a bit off the beaten path
for most readers)
Get my email addy from my web site

BTW: Joe has a good idea about using Solver it you want to avoid to the math
to rearrange the equation.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Chris" wrote in message
...
I am trying to solve the equation
T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38),
where "L, K & S" are constants and "i" is determined from a table based on
time. "T" is time in minutes. To solve the equation fully, once "T" is
solved for it is compared with the time value used in the table that "i"
was
determined from. If necessary, a new value of "i" is determined and the
equation is solved again. This is repeated until an acceptable tolerance
between "T" and the value used to determine "i" is reached. Is there an
easy
way to set this up in excel to run an iterative process with a table? Any
help would be greatly appreciated.

"Bernard Liengme" wrote:
We can find I by rearranging you equation to give
I =((11.4*L^0.5)*(K^0.52)*(S^-0.318)/T)^(1/0.38)
[....]
BTW: Joe has a good idea about using Solver it you want to
avoid to the math to rearrange the equation.

No, I wholeheartedly endorse an algebraic approach. I misread the OP. I
thought we had one equation and 4 unknowns. Looking back, I see that Chris
wrote "L, K & S are constants", so they do not vary. My bad!

(PS: If Chris does choose to use Solver, the set-up is different and much
simpler than I described.)

However....


I get the impression you have a table and you what to find quantity
i by interpolation

That is not how I understood the OP. Chris wrote: ``once "T" is solved for
it is compared with the time value used in the table that "i" was
determined from. If necessary, a new value of "i" is determined and the
equation is solved again. This is repeated until an acceptable tolerance
between "T" and the value used to determine "i" is reached``.

My interpretation is that "a new value of 'i' is determined" by selection
from the table. That is, "i" must be one of the discrete values in the
table. Chris is looking for the discrete value for "i" that results in the
closest value of "T".

I do not have time to give this much thought. But my interpretation is
right, I think one approach is:

1. In parallel with each "i" in the table, compute a corresponding "T" and
ABS(T-i). It might be possible to hide these columns or rows.

2. Use a formula (TBD) to find return the "i" in the table that corresponds
to the smallest ABS(T-i). I believe that's an INDEX(MATCH(MIN)) formula,
off the top of my head.


----- original message -----

"Bernard Liengme" wrote in message
...
Hi Chris,
Excel can be used to solve explicit equation (equations where the unknown
appears on both sides)
But your equation is implicit
We can find I by rearranging you equation to give
I =((11.4*L^0.5)*(K^0.52)*(S^-0.318)/T)^(1/0.38)

But I get the impression you have a table and you what to find quantity i
by interpolation
Happy to have private email from you (this looks a bit off the beaten path
for most readers)
Get my email addy from my web site

BTW: Joe has a good idea about using Solver it you want to avoid to the
math to rearrange the equation.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Chris" wrote in message
...
I am trying to solve the equation
T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38),
where "L, K & S" are constants and "i" is determined from a table based
on
time. "T" is time in minutes. To solve the equation fully, once "T" is
solved for it is compared with the time value used in the table that "i"
was
determined from. If necessary, a new value of "i" is determined and the
equation is solved again. This is repeated until an acceptable tolerance
between "T" and the value used to determine "i" is reached. Is there an
easy
way to set this up in excel to run an iterative process with a table?
Any
help would be greatly appreciated.

Hi. If I understand correctly...
You have data at discreet time intervals.

(11.4L^0.5)(K^0.52)(S^-0.318) is a constant ( k )

T = k * Table(T) ^ -.38

Where T is a discreet integer time, and Table is your lookup Table

It would appear to me that you take each table value 'n, and make a
helper column = k*n ^ -.38

The table value that is closest to its index value would be the closest
solution.

For example, suppose your equation from the 3 constants a

T = 18.66 * Table(T) ^ - .38

and your table had 10 values for time 1,2...10

{2, 4, 8, 16, 32, 64, 128, 256, 512, 1024}

Make a helper column 18.66 * n ^ -.38

{14.339, 11.0187, 8.46715, 6.50648, 4.99982, 3.84205, 2.95238,
2.26872, 1.74337, 1.33967}

Time 1 isn't too close to 1 at 14.33
Time 2 isn't too close to 2 at 11.01
But Time 5 is very close to 5 at 4.999

I would say for this example T = 5 would be the solution.

At time 5, the table value is 32.

18.66*32 ^ -.38

returns 4.99982

Which I think you want.

= = = = =
HTH
Dana DeLouis



On 3/8/10 11:56 AM, Chris wrote:
I am trying to solve the equation T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38),
where "L, K& S" are constants and "i" is determined from a table based on
time. "T" is time in minutes. To solve the equation fully, once "T" is
solved for it is compared with the time value used in the table that "i" was
determined from. If necessary, a new value of "i" is determined and the
equation is solved again. This is repeated until an acceptable tolerance
between "T" and the value used to determine "i" is reached. Is there an easy
way to set this up in excel to run an iterative process with a table? Any
help would be greatly appreciated.

"Chris" wrote:
once "T" is solved for it is compared with the time value used
in the table that "i" was determined from.

Please forgive the misdirection in my previous responses. It appears that
you want to find the i in the table that corresponds to the smallest
difference between computed T and table T, not ABS(T-i).


I am trying to solve the equation
T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38),
where "L, K & S" are constants and "i" is determined from a table
based on time. "T" is time in minutes.

As Bernard points out, your equation demonstrates a direct relation between
T and i. So I've been trying to figure out why the "time value used in the
table that i was determined from" would be any different from T computed by
the equation above.

I assume that you are dealing with something like the following situation.
You have a table of observed values of i measured at approximate times T.
Since these are real-world measurements, actual T and actual i might not
have exactly the algebraic relationship that we might expect based on the
equation for expected T. For example, acti might be +/-1% of the value that
we might expect (expi) when derived from actT by the equation above.

So I believe you are looking for acti whose actT is closest to expT. That
is, you want to find acti that corresponds to MIN(ABS(expT-actT)) or
MIN(ABS(expT-actT)/actT). I would opt for the latter, which is the minimum
relative difference.

If you agree, then I believe acti can be determined by a single formula
without iteration. However, for clarity, it might be useful to develop that
formula by using helper cells at first.

First, suppose that the constants L, K and S are in A2, B2 and C2
respectively. We can derive a constant A in D2:

=(11.4*A2^0.5)*(B2^0.52)*(C2^-0.318)

Thus, your equations become:

T = A * i^-0.38 --or-- T = A / i^0.38

i = (A / T) ^ (1/0.38)

Second, suppose that you have a table of 25 actT and corresponding acti
values in B5:C29; that is actT is B5:B29, and acti is in C5:C29.

Then expT, ABS(expT-actT) and ABS(expT-actT)/actT can be computed D5:F5 with
the following formulas, copied down through D29:F29:

D5: =$D$2*C5^-0.38
E5: =ABS(D5-B5)
F5: =E5/D5

Then the index MIN(ABS(expT-actT)/actT) and the corresponding acti can be
determined with the following array formulas[*]:

Min diff: =MATCH(MIN(F5:F29), F5:F29, 0)

acti: =INDEX(C5:C29, MATCH(MIN(F5:F29), F5:F29, 0))
[*] Enter an array formula by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, the formula will be surrounded by curly braces,
i.e. {=formula}. Note that you cannot type the curly braces yourself; Excel
inserts them to denote an array formula. If you make a mistake, select the
cell, press F2, edit as needed, then press ctrl+shift+Enter.

You can compute the corresponding acti without helper cells with the
following array formula:

=INDEX(C5:C29,
MATCH(MIN(ABS(B5:B29-D2*C5:C29^-0.38)/D2/C5:C29^-0.38),
ABS(B5:B29-D2*C5:C29^-0.38)/D2/C5:C29^-0.38, 0))

Hopefully you can leverage this paradigm for precisely what you are trying
to achieve, i.e. "an acceptable tolerance" between expT and actT.


----- original message -----

"Chris" wrote in message
...
I am trying to solve the equation
T=(11.4L^0.5)(K^0.52)(S^-0.318)(i^-0.38),
where "L, K & S" are constants and "i" is determined from a table based on
time. "T" is time in minutes. To solve the equation fully, once "T" is
solved for it is compared with the time value used in the table that "i"
was
determined from. If necessary, a new value of "i" is determined and the
equation is solved again. This is repeated until an acceptable tolerance
between "T" and the value used to determine "i" is reached. Is there an
easy
way to set this up in excel to run an iterative process with a table? Any
help would be greatly appreciated.