# Maths

September 8, 2009 2:12 PM Subscribe

Help me solve this equation! (please)

Given this equation:

. . . . . d * (1 - e^(-k * a)) * e^(-k * t)

C = -----------------------------------------

. . . . . a * v * k * (1 - e^(-k * h))

I need to solve for k. Any takers?

Please ignore the periods; it wouldn't except the spacing.

Given this equation:

. . . . . d * (1 - e^(-k * a)) * e^(-k * t)

C = -----------------------------------------

. . . . . a * v * k * (1 - e^(-k * h))

I need to solve for k. Any takers?

Please ignore the periods; it wouldn't except the spacing.

Response by poster: Ha, no. It's a project I'm working on for myself. I'm building the equation into an excel spreadsheet, but the only way I can set it up right is to solve for k.

It's almost more of an academic exercise to see what I can do with excel, and this equation is the last hurdle.

posted by jjbb at 2:19 PM on September 8, 2009

It's almost more of an academic exercise to see what I can do with excel, and this equation is the last hurdle.

posted by jjbb at 2:19 PM on September 8, 2009

Response by poster: Ah! Oversight by me. a=2! Hopefully that helps a little.

posted by jjbb at 2:29 PM on September 8, 2009

posted by jjbb at 2:29 PM on September 8, 2009

a = 2 is not gonna help. it's the k on the bottom. polynomials and exponentials don't play nice together. e.g. x = 1 - e^{-2x}.

posted by water bear at 2:31 PM on September 8, 2009

posted by water bear at 2:31 PM on September 8, 2009

Well, I put it into Mathematica for you, and it said tough luck, so ... try a numerical solution, maybe. I vaguely recall Excel can do numerical solutions, but it may require a special package.

posted by Comrade_robot at 2:33 PM on September 8, 2009

posted by Comrade_robot at 2:33 PM on September 8, 2009

Do these variables represent particular cells references in Excel?

If so, give us those cell references and the algebraic syntax is fairly easy to convert into Excel syntax.

posted by dfriedman at 2:37 PM on September 8, 2009

If so, give us those cell references and the algebraic syntax is fairly easy to convert into Excel syntax.

posted by dfriedman at 2:37 PM on September 8, 2009

x = e^{-2x}. (d'oh!)

posted by water bear at 2:37 PM on September 8, 2009

posted by water bear at 2:37 PM on September 8, 2009

Response by poster: Yes, they will represent cells. I don't have them fully set up right now, but can we just use arbitrary place holders?

k is the cell I want to compute

d = E18

a = E19

t = E20

v = E21

h = E22

posted by jjbb at 2:44 PM on September 8, 2009

k is the cell I want to compute

d = E18

a = E19

t = E20

v = E21

h = E22

posted by jjbb at 2:44 PM on September 8, 2009

You need to use Excel's Solver tool for this type of application.

See the Google link I give for more information on it.

Even with Solver you're not going to be able to find a value for k unless you know what C is equal to.

posted by dfriedman at 2:49 PM on September 8, 2009

See the Google link I give for more information on it.

Even with Solver you're not going to be able to find a value for k unless you know what C is equal to.

posted by dfriedman at 2:49 PM on September 8, 2009

You may find this discussion group helpful as well.

posted by dfriedman at 2:54 PM on September 8, 2009

posted by dfriedman at 2:54 PM on September 8, 2009

Response by poster: c is also a cell reference, i forgot to put that one. Does that make a difference?

posted by jjbb at 2:59 PM on September 8, 2009

posted by jjbb at 2:59 PM on September 8, 2009

I'm not going to do this one for you, but if the products (k*a), (k*t), or (k*h) are much smaller than one, you can use the Taylor series approximation that, e.g., e^{-ka) = 1 - (ka) + (ka)^2/2 - ...

Without approximation, you can put the whole Taylor series in and express your problem completely in terms of polynomials, which might be helpful to you.

posted by fatllama at 3:36 PM on September 8, 2009

Without approximation, you can put the whole Taylor series in and express your problem completely in terms of polynomials, which might be helpful to you.

posted by fatllama at 3:36 PM on September 8, 2009

Response by poster: (k * a) will be 0.1-0.3 typically, but (k*t) and (k*h) will likely be 1-2.

Link still doesn't seem to be working..

posted by jjbb at 3:41 PM on September 8, 2009

Link still doesn't seem to be working..

posted by jjbb at 3:41 PM on September 8, 2009

Response by poster: Yeah, did that.. I haven't taken a Math class in 7 years. I can barely handle natural logs at this point. The taylor series approximation article on wikipedia gave me a headache.

posted by jjbb at 3:56 PM on September 8, 2009

posted by jjbb at 3:56 PM on September 8, 2009

Try this. Rearrange your equation so it looks like this:

k = (a*c*v/d) * (1-e^-ka)*(1-e^-kh)*e^-kt

Now, make one column (column A, for example) called "k", and have it start at 0, and increment by 0.01 (or something small) to 3.

Now make a column next to it (column B), equal to

(a*c*v/d) * (1-e^-ka)*(1-e^-kh)*e^-kt

For each value of k.

Now make a third column (column C) which is equal to the absolute value of the first minus the second columns (i.e. Cn = ABS(Bn-An)).

Now, make a cell calculate (MIN(C1:C300)). Then you can use VLOOKUP or something to find the k-value that best solves the equation (I'm a little fuzzy on the syntax for that).

This process is essentially numerically solving the equation manually. There's probably a fancy way to do it automatically, but this way is pretty easy.

posted by Salvor Hardin at 4:27 PM on September 8, 2009

k = (a*c*v/d) * (1-e^-ka)*(1-e^-kh)*e^-kt

Now, make one column (column A, for example) called "k", and have it start at 0, and increment by 0.01 (or something small) to 3.

Now make a column next to it (column B), equal to

(a*c*v/d) * (1-e^-ka)*(1-e^-kh)*e^-kt

For each value of k.

Now make a third column (column C) which is equal to the absolute value of the first minus the second columns (i.e. Cn = ABS(Bn-An)).

Now, make a cell calculate (MIN(C1:C300)). Then you can use VLOOKUP or something to find the k-value that best solves the equation (I'm a little fuzzy on the syntax for that).

This process is essentially numerically solving the equation manually. There's probably a fancy way to do it automatically, but this way is pretty easy.

posted by Salvor Hardin at 4:27 PM on September 8, 2009

You can change the increment of the k-value column if you need a finer/more accurate calculation.

posted by Salvor Hardin at 4:28 PM on September 8, 2009

posted by Salvor Hardin at 4:28 PM on September 8, 2009

The solver tool is what you want. See here for an example of how to use it. "k" will be the only cell you allow it to change.

posted by a robot made out of meat at 5:29 PM on September 8, 2009

posted by a robot made out of meat at 5:29 PM on September 8, 2009

Personally I think you are going to have to numerically solve this rather then analyticly.

If k is purely imaginary the equation you have up there looks like the equation for adding a large number of sin waves together where each one is out of phase. In this case solving for k while possible is really nasty.

If a is a scalar multiple of h ie (a=n*h) your equation could be a geometric progression with the first term e^(-k*t) and the ratio of e^(-k h) this done out with a 1/k in front looks like the Taylor expansion of a familiar function but I cant put my finger on it now.

Other then that you might get somewhere by using the expression:

1-e^(a k)= e^((a k)/2) * (e^(-(a k)/2) - e^((a k)/2))

And playing around with it.

posted by metex at 5:32 PM on September 8, 2009

If k is purely imaginary the equation you have up there looks like the equation for adding a large number of sin waves together where each one is out of phase. In this case solving for k while possible is really nasty.

If a is a scalar multiple of h ie (a=n*h) your equation could be a geometric progression with the first term e^(-k*t) and the ratio of e^(-k h) this done out with a 1/k in front looks like the Taylor expansion of a familiar function but I cant put my finger on it now.

Other then that you might get somewhere by using the expression:

1-e^(a k)= e^((a k)/2) * (e^(-(a k)/2) - e^((a k)/2))

And playing around with it.

posted by metex at 5:32 PM on September 8, 2009

Yikes, wrong variable. As others have said, it looks to be a numerical solution. Ignore that link above.

posted by Aanidaani at 5:51 PM on September 8, 2009

posted by Aanidaani at 5:51 PM on September 8, 2009

Yeah, the term k*e^k in the denominator means the whole thing is transcendental and doesn't have an ordinary solution (though you might, with some muscling, be able to write a solution in terms of Lambert's W function).

I would treat this like Salvor Hardin suggests, but I would do less algebra and use a graph rather than a lookup table. I would get rid of the denominators and compute

posted by fantabulous timewaster at 9:33 PM on September 8, 2009

I would treat this like Salvor Hardin suggests, but I would do less algebra and use a graph rather than a lookup table. I would get rid of the denominators and compute

Cav k (1-e^(-kh))and

d (1-e^(-ka)) e^(-kt).These two terms are equal when your equation is satisfied. Plot their difference as a function of k and look for zero crossings. Fiddle with the minimum, maximum, and step in k to read the zero crossing off the graph with whatever precision you like. This is slower than a computerized iterator that does the same thing, but it is faster than learning how to use such an iterator. And if you wind up using an iterator, this is a useful way to decide whether you believe its results.

posted by fantabulous timewaster at 9:33 PM on September 8, 2009

Let f[c, d, a, v, h, t] be a function that gives a solution of your equation for any value of the 6 parameters. In Mathematica, you can use FindRoot to define f. Thanks Aanidaani for pointing out tinypic. I have been looking for just such a site.

posted by hAndrew at 10:23 PM on September 8, 2009

posted by hAndrew at 10:23 PM on September 8, 2009

Response by poster: Anyone know how to put this into excel??

posted by jjbb at 1:16 AM on September 9, 2009

posted by jjbb at 1:16 AM on September 9, 2009

Sure. Up at the top, store C, a, v, d, h, t in cells. You'll refer to them absolutely, with the dollar symbols, so the references don't move as you cut and paste. Also store k_min and k_step, which you'll use to construct guesses for k.

Now make a column of "n" that counts from zero to the number of points you want to graph at once. Say you start this in A10.

Next to "n" (say, B10) make a column "k" with the expression =$k_min + $k_step * A10. Substitute the right cell references for k_min and k_step. Copy and paste down. You should now have a range of guesses for k.

Next to "k" (say, C10 and D10) make columns "left hand side" and "right hand side" with the two expressions from my other comment. Use relative references back to k, absolute references to everyone else. Make one more column E10 = C10-D10.

Now plot column E on the vertical axis and column B on the horizontal axis. Change k_min and k_step and the plot limits should move around. Find a zero crossing. That is the value of k that solves your equation.

posted by fantabulous timewaster at 9:20 AM on September 9, 2009

Now make a column of "n" that counts from zero to the number of points you want to graph at once. Say you start this in A10.

Next to "n" (say, B10) make a column "k" with the expression =$k_min + $k_step * A10. Substitute the right cell references for k_min and k_step. Copy and paste down. You should now have a range of guesses for k.

Next to "k" (say, C10 and D10) make columns "left hand side" and "right hand side" with the two expressions from my other comment. Use relative references back to k, absolute references to everyone else. Make one more column E10 = C10-D10.

Now plot column E on the vertical axis and column B on the horizontal axis. Change k_min and k_step and the plot limits should move around. Find a zero crossing. That is the value of k that solves your equation.

posted by fantabulous timewaster at 9:20 AM on September 9, 2009

This thread is closed to new comments.

posted by rokusan at 2:13 PM on September 8, 2009