Complex formula

[Chuck]

Can excel solve the following type of formula:

D=1.3[(a*b)^0.625/(a+b)^0.250]

When the values of D and a are known?

Thanks

 

[Kevin Stecyk]

Chuck,

Rearrange your equation to....

Z=D-1.3[(a*b)^0.625/(a+b)^0.250]

Z should be 0 when you have the proper value for "b". Now just use goal
seek to find "a".

You can let A1=a
B1=b
D1=D

E1=Z

Create your formula in a cell, and then use goal seek to solve for the value
of Z.

Hope that helps.

Regards,
Kevin



Can excel solve the following type of formula:

D=1.3[(a*b)^0.625/(a+b)^0.250]

When the values of D and a are known?

Thanks

 

[Kevin Stecyk]

Should be....

Create your formula in a cell, and then use goal seek to solve for the value
of b knowing that Z=0

 

[Chuck]

Thank you Kevin. Goal seek works great. I don't understand the
significance of assigning z=0. I did insert the formula without z.
Using actual cells for the formula I got:
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25))
and told Goal_Seek to solve the equation to be equal to 0 as it changes B.
It gives me the correct answer for B.

I need to run this same operation on a column of options for a. This is
very time consuming to run goal seek on each value. (I have also run the
solver add-in which seems to do the same thing.) Is there a way to get
excel to automatically run the same analysis on the entire column of values
for $B13 (approximately 20-30) for every time I vary the value for $E$4?

Thanks
Chuck

Should be....

Create your formula in a cell, and then use goal seek to solve for the value
of b knowing that Z=0

Chuck,

Rearrange your equation to....

Z=D-1.3[(a*b)^0.625/(a+b)^0.250]

Z should be 0 when you have the proper value for "b". Now just use goal
seek to find "a".

You can let A1=a
B1=b
D1=D

E1=Z

Create your formula in a cell, and then use goal seek to solve for the value
of Z.

Hope that helps.

Regards,
Kevin



Can excel solve the following type of formula:

D=1.3[(a*b)^0.625/(a+b)^0.250]

When the values of D and a are known?

Thanks

 

[Kevin Stecyk]

Chuck,

Using actual cells for the formula I got:
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25))

I believe you created your own "z" by that equation. Z=
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25)).

And now you have set it equal to 0. We are saying the same thing.

As I look at it now, you didn't have to do that. You could have simply....

D=1.3[(a*b)^0.625/(a+b)^0.250]

Now when you use goal seek, just make sure that the RHS = the "D" value.
Before you brought everything to one side and made it equal to 0.

I think what you are saying is you have multiple equations and multiple
unknowns. So long as you have an equal number of equations and unknowns,
yes you can use Excel to solve it. I believe in your case, you will have a
unique "b" value for every equation.

I am having difficulty explaining how to do it, though once you see how to
do it, the solution jumps out at you.

Perhaps you could send me your spreadsheet, and I could show you how to do
it?

I will send you an e-mail, and if you like, you can send me your
spreadsheet, and I will show you how?

Regards,
Kevin

Thank you Kevin. Goal seek works great. I don't understand the
significance of assigning z=0. I did insert the formula without z.
Using actual cells for the formula I got:
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25))
and told Goal_Seek to solve the equation to be equal to 0 as it changes B.
It gives me the correct answer for B.

I need to run this same operation on a column of options for a. This is
very time consuming to run goal seek on each value. (I have also run the
solver add-in which seems to do the same thing.) Is there a way to get
excel to automatically run the same analysis on the entire column of values
for $B13 (approximately 20-30) for every time I vary the value for $E$4?

Thanks
Chuck

Should be....

Create your formula in a cell, and then use goal seek to solve for the value
of b knowing that Z=0

Chuck,

Rearrange your equation to....

Z=D-1.3[(a*b)^0.625/(a+b)^0.250]

Z should be 0 when you have the proper value for "b". Now just use goal
seek to find "a".

You can let A1=a
B1=b
D1=D

E1=Z

Create your formula in a cell, and then use goal seek to solve for the value
of Z.

Hope that helps.

Regards,
Kevin



Can excel solve the following type of formula:

D=1.3[(a*b)^0.625/(a+b)^0.250]

When the values of D and a are known?

Thanks

 

[Dana DeLouis]

Just a technique. Sometimes I find it easier to read if I give Range Names
to the equation. For example, have 3 columns named with a name like
"a","b", & "d."

Then, use an equation like:
=d-(1.3*POWER(a*b,0.625))/(POWER(a+b,0.25))

Fill in "a" & "d", and have Goal Seek set this equal to zero by changing
"b."

The returned accuracy of "b" is only good to a few digits. You can
sometimes increase this accuracy a little by using Solver. Just have Solver
set this equation to zero by adjusting b. However, you will want to adjust
the Solver options so that it converges more. (Adjust Precision &
Convergence.). This should get you an extra digit or two of accuracy.

If interested, another technique to bump up the accuracy of a solution that
"sometimes" works is the following.

Enter another equation. This is your equation rearranged to the
following...
= -100000000*a^2*d^8 + b*(-200000000*a*d^8 + b*(815730721*a^5*b^3 -
100000000*d^8))

Use Goal Seek on your normal equation to return a value of "b" as you
normally do.
Then, run Goal Seek again on the above equation (set to zero also) by
adjusting b again.
Running Goal Seek a second time on an adjusted equation like the one above
can "sometimes" greatly increase the accuracy of an answer.
HTH.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Thank you Kevin. Goal seek works great. I don't understand the
significance of assigning z=0. I did insert the formula without z.
Using actual cells for the formula I got:
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25))
and told Goal_Seek to solve the equation to be equal to 0 as it changes B.
It gives me the correct answer for B.

I need to run this same operation on a column of options for a. This is
very time consuming to run goal seek on each value. (I have also run the
solver add-in which seems to do the same thing.) Is there a way to get
excel to automatically run the same analysis on the entire column of values
for $B13 (approximately 20-30) for every time I vary the value for $E$4?

Thanks
Chuck

Should be....

Create your formula in a cell, and then use goal seek to solve for the value
of b knowing that Z=0

Chuck,

Rearrange your equation to....

Z=D-1.3[(a*b)^0.625/(a+b)^0.250]

Z should be 0 when you have the proper value for "b". Now just use goal
seek to find "a".

You can let A1=a
B1=b
D1=D

E1=Z

Create your formula in a cell, and then use goal seek to solve for the value
of Z.

Hope that helps.

Regards,
Kevin



Can excel solve the following type of formula:

D=1.3[(a*b)^0.625/(a+b)^0.250]

When the values of D and a are known?

Thanks

 

[Chuck]

Thanks Kevin

I emailed the spreadsheet to you. I also tried working on a vbasic routine
to update the entire column. I got a macro to work to update a cell at a
time. As you can see, I don't have much experience with VBasic.

Thanks

Chuck

Chuck,

Using actual cells for the formula I got:
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25))

I believe you created your own "z" by that equation. Z=
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25)).

And now you have set it equal to 0. We are saying the same thing.

As I look at it now, you didn't have to do that. You could have simply....

D=1.3[(a*b)^0.625/(a+b)^0.250]

Now when you use goal seek, just make sure that the RHS = the "D" value.
Before you brought everything to one side and made it equal to 0.

I think what you are saying is you have multiple equations and multiple
unknowns. So long as you have an equal number of equations and unknowns,
yes you can use Excel to solve it. I believe in your case, you will have a
unique "b" value for every equation.

I am having difficulty explaining how to do it, though once you see how to
do it, the solution jumps out at you.

Perhaps you could send me your spreadsheet, and I could show you how to do
it?

I will send you an e-mail, and if you like, you can send me your
spreadsheet, and I will show you how?

Regards,
Kevin

Thank you Kevin. Goal seek works great. I don't understand the
significance of assigning z=0. I did insert the formula without z.
Using actual cells for the formula I got:
=$E$4-1.3*(POWER(($B13*$C13),0.625)/POWER(($B13+$C13),0.25))
and told Goal_Seek to solve the equation to be equal to 0 as it changes B.
It gives me the correct answer for B.

I need to run this same operation on a column of options for a. This is
very time consuming to run goal seek on each value. (I have also run the
solver add-in which seems to do the same thing.) Is there a way to get
excel to automatically run the same analysis on the entire column of values
for $B13 (approximately 20-30) for every time I vary the value for $E$4?

Thanks
Chuck

Should be....

Create your formula in a cell, and then use goal seek to solve for the value
of b knowing that Z=0


Chuck,

Rearrange your equation to....

Z=D-1.3[(a*b)^0.625/(a+b)^0.250]

Z should be 0 when you have the proper value for "b". Now just use goal
seek to find "a".

You can let A1=a
B1=b
D1=D

E1=Z

Create your formula in a cell, and then use goal seek to solve for the
value
of Z.

Hope that helps.

Regards,
Kevin



Can excel solve the following type of formula:

D=1.3[(a*b)^0.625/(a+b)^0.250]

When the values of D and a are known?

Thanks