Because I hate myself [Excel] (Update: Now with more Chemistry!)

ceres

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.

LaOs

I'm in Excel 2007, have never heard of Goal Seek, but in the F1 help menu, there's a step-by-step example for using Goal Seek to determine the interest rate required if you know how much you want to borrow, what you can pay, and how long you have to pay it off. Goal Seek, in this example, helps you find the Interest Rate. Looks like you set up values in different cells and then use Goal Seek to figure things out. I'm not really sure what it all was saying, because I'm unfamiliar with it, but could that help (if you haven't checked that out)?

Tzyr

Are you sure the formula to your problem correct?

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

This 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:

=((C2^2)*((1+8*C2)^8))/((0.01-C2)*((0.01-(2*C2))^2))

where C2 is the cell I used for the variable.

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:

=((C2^2)*((1+8*C2)^8))/((0.01-C2)*((0.01-(2*C2))^2))

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.

ecco the dolphin

Could you double check your equation please?

I'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.

ceres

etd, your first answer is what I got putting it into Excel, and using OO I also don't get an answer at all. I've been wondering if maybe it's my formatting, but everyone else gets the same answer.

The 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.

DJ-99

=((E2^2)*(1+8*E2)^8)/((0.01-E2)*(0.01+2*E2)^2)

Where 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.

tsmvengy

Helpful?

http://tinyurl.com/mxga5k

ceres

-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.

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

Jebus314

Tis been a while since I have tried to master the excel, but my suggestion would be to see if you can use solver. If it is already loaded just go to Tools -> solver. Otherwise try going to Tools -> Add-Ins and checking the box for solver; this should make solver appear in Tools -> solver. Solver is much better than goal seek because it gives you the option of searching for a max or min as well as a value, and you can set constraints for the variable cell. So you can set up solver the same as goal seek but under the section marked "subject to the constraints" add in a constraint that says your variable cell must be greater than 0. I didn't look at the actual formula so I can't guarantee there isn't something else wrong, but this will allow you to make sure the answer is positive.

ceres

Huh. That might just be what I need.

ecco the dolphin

ceres wrote: »

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.

I 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...

Infidel

There's gotta be something wrong here.

Back 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.

ceres

Hrm... I'm so confused. I'm inclined to believe something *is* wrong, but if it is I can't find it, and I'm just not knowledgeable enough in the course matter to be able to pinpoint it.

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