ceres
When the last moon is cast over the last star of morningAnd the future has past without even a last desperate warningRegistered User, Moderator Mod Emeritus

I need to learn how to solve a rather complicated problem iteratively using Excel's Goal Seek.

The problem goes something like this:

1*10^11=[x^2(1+8x)^8]/[(.01-x)(.01-2x)^2]

The answer needs to be positive, and should be in the ballpark of 1.308*10^-7. Whenever I try to input this formula, Goal Seek gives me back a number that is comparatively large and quite negative.

I really don't know Excel very well, and I have this book that, while it has an example spreadsheet with a similar problem, the spreadsheet is full of products and is generally not explained very well. I have no idea how the information in it relates to what I actually need to do.

How do I use Excel to do this? Do I need multiple cells for this formula? I'm just not sure how to put it all together.

**EDIT:**

-4.36ish is what I keep getting, and it cannot physically possibly be right.

This needs some context.

This is supposed to be the formula for finding the equilibrium concentrations of bromate, chromium, bromine, dichromate, and hydrogen after the reaction

BrO3(-) + 2Cr(3+) +4H20 <--> Br(-) + Cr2O7(2-) + 8H(+)

with the starting concentrations

.01M BrO3(-)

.01M Cr(3+)

1.00M H(+)

and K=1.0*10^11

You can't have negative concentration, so x can't be negative despite the fact that it's the only consistent answer I've seen at this point. You can't neglect x for this problem because the numbers are too big, and you can't use the quadratic formula because there's a power of*8* in there. Normally you just cheese it by treating the stated products as the stated reactants and doing the problem in reverse to make the numbers very small instead of very large so you can neglect x.

What my teacher wants us to learn to do (and he doesn't care how we do it) is to learn to use Excel to solve this problem iteratively. Unfortunately, I think I must have something wrong because even though the formula is right according to him, I keep getting this -4.blaaah answer whenever I punch it in, and so does everyone else.

tl:dr; What I really need is someone with experience using Excel for this kind of purpose to help me figure out why I'm getting the answer I'm getting and not the 1.308*10^-7 that I should be getting. My hunch is that I'm not using the software properly, and the help for Excel just.. isn't thorough enough.

The problem goes something like this:

1*10^11=[x^2(1+8x)^8]/[(.01-x)(.01-2x)^2]

The answer needs to be positive, and should be in the ballpark of 1.308*10^-7. Whenever I try to input this formula, Goal Seek gives me back a number that is comparatively large and quite negative.

I really don't know Excel very well, and I have this book that, while it has an example spreadsheet with a similar problem, the spreadsheet is full of products and is generally not explained very well. I have no idea how the information in it relates to what I actually need to do.

How do I use Excel to do this? Do I need multiple cells for this formula? I'm just not sure how to put it all together.

-4.36ish is what I keep getting, and it cannot physically possibly be right.

This needs some context.

This is supposed to be the formula for finding the equilibrium concentrations of bromate, chromium, bromine, dichromate, and hydrogen after the reaction

BrO3(-) + 2Cr(3+) +4H20 <--> Br(-) + Cr2O7(2-) + 8H(+)

with the starting concentrations

.01M BrO3(-)

.01M Cr(3+)

1.00M H(+)

and K=1.0*10^11

You can't have negative concentration, so x can't be negative despite the fact that it's the only consistent answer I've seen at this point. You can't neglect x for this problem because the numbers are too big, and you can't use the quadratic formula because there's a power of

What my teacher wants us to learn to do (and he doesn't care how we do it) is to learn to use Excel to solve this problem iteratively. Unfortunately, I think I must have something wrong because even though the formula is right according to him, I keep getting this -4.blaaah answer whenever I punch it in, and so does everyone else.

tl:dr; What I really need is someone with experience using Excel for this kind of purpose to help me figure out why I'm getting the answer I'm getting and not the 1.308*10^-7 that I should be getting. My hunch is that I'm not using the software properly, and the help for Excel just.. isn't thorough enough.

And it seems like all is dying, and would leave the world to mourn

0

## Posts

LaOsonThis will only be positive if x < 0.01. Which of course would be correct if the solution is supposed to be 1.308*10^-7

(Just wanted you to go over the formula to see if you missed something)

Now, I do not have Excel so I tried it with OpenOffice 2.4 (I really should get 3). Anyway, I had to change your formula around for you had what I think assumed multiplication but were not:

I put extra brackets in..though I know that it should be used order of operation...but meh, habbit.

Anyway, I was unable to get an answer. So not sure if it was a limitation of my spreadsheet, or if something is wrong with the formula.

To give you an idea, I had 2 cells, one with the formula:

And one sell I just put a random number in it (just put 1 as default), that random cell being C2. I selected the formula cell, chose goal seek; added 100000000000 as the target, then selected that random number cell as the variable. I could not actually use 1*10^11 as the value...I guess it would not calculate it.

Not sure if that helps, but I think your problem was the formula had some inplicit multiplication that at least OpenOffice does not support.

Good luck.

TzyronI've just tried using Mathematica to solve the equation, and it claims that these are the possible solutions (rounded):

{x -> -4.36554},

{x -> -2.77672 - 3.2998 i},

{x -> -2.77672 + 3.2998 i},

{x -> 0.00499987},

{x -> 0.00500013},

{x -> 0.01},

{x -> 0.793292 - 4.1148 i},

{x -> 0.793292 + 4.1148 i},

{x -> 3.6562 - 1.83127 i},

{x -> 3.6562 + 1.83127 i}

None of which are as small as 10^-7, I'm afraid.

ecco the dolphinonThe problem should *read*

1*10^11 = x times x times (1+8x)^8 all over (.01-x)(.01+2x)^2

I double-checked it, and I double-checked it with my teacher, and the equation *should* be right. This is driving me crazy, because it means that SOMEBODY is wrong. Knowing that somebody is probably me isn't helping.

ceresonWhere E2 = x.

That gives me -4.36277 in Excel. So, assuming that convoluted formula is right, I'm sure that -4.36 is the right answer. Hope that helps.

DJ-99onhttp://tinyurl.com/mxga5k

tsmvengyonThis needs some context.

This is supposed to be the formula for finding the equilibrium concentrations of bromate, chromium, bromine, dichromate, and hydrogen after the reaction

BrO3(-) + 2Cr(3+) +4H20 <--> Br(-) + Cr2O7(2-) + 8H(+)

with the starting concentrations

.01M BrO3(-)

.01M Cr(3+)

1.00M H(+)

and K=1.0*10^11

You can't have negative concentration, so x can't be negative despite the fact that it's the only consistent answer I've seen at this point. You can't neglect x for this problem because the numbers are too big, and you can't use the quadratic formula because there's a power of

8in there. Normally you just cheese it by treating the stated products as the stated reactants and doing the problem in reverse to make the numbers very small instead of very large so you can neglect x.What my teacher wants us to learn to do (and he doesn't care how we do it) is to learn to use Excel to solve this problem iteratively. Unfortunately, I think I must have something wrong because even though the formula is right according to him, I keep getting this -4.blaaah answer whenever I punch it in, and so does everyone else.

tl:dr; What I really need is someone with experience using Excel for this kind of purpose to help me figure out why I'm getting the answer I'm getting and not the 1.308*10^-7 that I should be getting. My hunch is that I'm not using the software properly, and the help for Excel just.. isn't thorough enough.

Also, tsm, this site is my newest bookmark as it is extremely cool.

ceresonJebus314onceresonI really think that the equation or expected answer is wrong. If I'm reading your question right, you said that substituing 1.308*10^-7 into the equation will give 10^11 at the output.

I plotted x vs your equation for 1.0 * 10^-7 to 2.0 * 10^-7 and the graph goes from 1.0*10^-8 to 4 * 10^-8. It doesn't head anywhere near 10^11 - it's 19 orders of magnitude away...

ecco the dolphinonBack of the envelope, the bottom of your division is effectively .01 times .01 ^ 2, since x is supposedly trivially small, and you would have roughly 1 * 10^-6 as your divisor, which is the same as a 1 * 10^6 multiplier.

That means to reach the LHS of 1 * 10^11, the top of the RHS would have to be roughly 1 * 10^5.

x^2 times (1+8x)^8, now 1 + a trivial x raised 8 times is going to be not much more than 1, so lets disregard that whole term, we're left with x^2 which is clearly not anywhere close to 1 * 10^5 for our "known" x.

InfidelonI'll keep trying to work with it, and maybe ask him again and see what he says.

cereson