Circular Reference will not solve #VALUE! or #DIV/0!

[Guest]

The following will not solve (or start to solve?):

H4=I4+J4+K4
I4=60.73
J4=constant/(constant*M4)
L4=(I4+constant)/2
M4=function(L4)

I get this error on cells H4, J4, L4, and M4: #VALUE!

Sometimes J4 gets a #DIV/0! error.

I know this converges, as other programs using these formulae will converge.

Does excel assume 0 for inital guess? -this would explain the #DIV/0 error.
Is it posible to assign an initial guess for the cells in question?

Iteration is enabled with 100 maximum iterations, and 0.1 maximum change.

I appreciate any help on this!

Peter

 

[JE McGimpsey]

J4 will return a #DIV/0 error if M4 is zero. M4 depends on I4, so
again, without knowing what "function()" is, it's impossible to know
what's going on, except that M4 returning #VALUE! probably means that L4
returns the wrong type of argument.

L4 would appear to return #VALUE! only when "constant" is not numeric.

You have no circular references here, unless your missing K4 function
creates one, so it's not surprising that setting Iteration doesn't solve
the problem.

What are your exact formulae?

 

[Guest]

My spreadsheet contained an error: The equation for L4 in my original post
referenced an incorrect cell. The exact formulae are:

H4=I4+J4+K4
I4=60.73
J4=49.0/(2*254*M4*0.342)
K4=23.128
L4=(I4+H4+K4)/2
M4=(-1.8484E-15*L4^6) + (9.4220E-13*L4^5) - (1.5108E-10*L4^4) +
(5.7474E-09*L4^3) + (7.0730E-07*L4^2) - (5.5519E-5*L4) + 1.6117E-2

After making the correction it solved immediately!

Thanks for your help.

Peter

 

[Dana DeLouis]

Hi. Just curious. With iteration turned on, I get a value for M4 of
0.0157487504127926. Is that what you have also?
I couldn't get any other values. However, If I'm not mistaken, I believe
there are 3 Real solutions, and 4 Imaginary solutions.
The other 2 Reals are:
0.00076474810382905
and
-0.00077691667356448

Hope I got that right. Are I4 & K4 variable inputs ??

 

[Guest]

Hi Dana,

I get a value of .015163... for M4.

The other real values for M4 correspond to what values of H4? Out of
curiosity, How did you reach the answer- through Excel?

Do you know if Excel uses an "initial guess" for the circular references to
initiate the iteration?

I4 and K4 are variables, I4 is a temperature in degrees C, which is derived
from user input, and K4 is actually a delta-T in Celcius, but also varies
with user input.

If you are familiar with Peltier (thermoelectric or TEC) devices, the
equation is the device performance equation simplified for 0 W TEC driving
power. It calculates the ambient temperature (H4) at which a TEC "cooling" a
device requiring a thermal dissipation (49 in the J4 equation- input by the
user) can hold that device at the required temperature (I4- also input by
user) with 0 W driving power.

There are limits to the ambient and control temperatures for which this
equation holds true. Outside of those limits, the equation is not accurate,
but this may not matter because the device will probably stop working anyway!

You may be asking, why not just use the thermal conductance of the TEC, its
heat sink, and device? The answer is that the conductance of the TEC is
dependent on the average temperature of the TEC. This the average
temperature is accounted for in the M4 equation and the delta-T of the TEC is
calculated in equation J4.

More than you ever wanted to know!

Peter

 

[Dana DeLouis]

Hi Peter. I was just curious on your equation, that's all. Earlier you
mentioned that.



I had set Maximum change to 0.00000000000001, so Excel looped a few more
times to narrow down on the solution.



I was just messing around. I noticed that M4 only depends on L4. Its
general equation is: a L4^6 + b*L4^5 + c*L4^4.



But it looks like L4 depends only on M4.

I4 & K4 are Constants



L4 = (I4 + K4) + 0.141018556890914 / M4



So, it looks to me like a 7th degree polynomial for M4. (i.e. 7 solutions)

Yes, another program suggested 3 Real solutions for M4 from above, one of
which was the same as Excel's. Once you have M4, L4 and the other variables
are then easy to calculate. I couldn't get Excel to get the other two
numbers though.

Again, I was just messing around with your equation.

 

[Guest]

Dana,

Would you mind sending the other two real solutions? You can either post it
or email me directly by replacing "donotspam" with "fibertek" in the email
address listed in my profile.

I'm interested to see if these real solutions were within the possibilities
of the real world application. This might explain a few things...

Peter

 

[Dana DeLouis]

I'm interested to see if these real solutions were within the

possibilities
of the real world application. This might explain a few things...

Hi Peter. Here's what I got... Hope this helps in some way. :>)
(Hope I did this right also!)

If we start with a guess that
M4 is 0.0007647481038290573, then
L4 is 268.25670093804973
If I plug L4 back into the equation for M4, I get the same answer for M4.

Therefore...
H4: 452.6554018760819
J4: 368.7974018760819

The other solution I get is:

M4: -0.00077691667356448
L4: -97.652529622080
H4: -279.1630592441668
J4: -363.0210592441668

As you can see, the second solution has all negative numbers, so they may
not be feasible.
Hope this helps! :>)
--
Dana DeLouis
Win XP & Office 2003

Dana,

Would you mind sending the other two real solutions? You can either post
it
or email me directly by replacing "donotspam" with "fibertek" in the email
address listed in my profile.

I'm interested to see if these real solutions were within the
possibilities
of the real world application. This might explain a few things...

Peter

<snip>