Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Circular Reference will not solve #VALUE! or #DIV/0!
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 |
#2
|
|||
|
|||
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? In article , LaserDude wrote: 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 |
#3
|
|||
|
|||
My spreadsheet contained an error: The equation for L4 in my original post
referenced an incorrect cell. The exact formulae a 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 "JE McGimpsey" wrote: 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? In article , LaserDude wrote: 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 |
#4
|
|||
|
|||
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 a 0.00076474810382905 and -0.00077691667356448 Hope I got that right. Are I4 & K4 variable inputs ?? -- Dana DeLouis "To understand recursion, one must first understand recursion." Win XP & Office 2003 "LaserDude" wrote in message ... My spreadsheet contained an error: The equation for L4 in my original post referenced an incorrect cell. The exact formulae a 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 "JE McGimpsey" wrote: 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? In article , LaserDude wrote: 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 |
#5
|
|||
|
|||
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" wrote: 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 a 0.00076474810382905 and -0.00077691667356448 Hope I got that right. Are I4 & K4 variable inputs ?? -- Dana DeLouis "To understand recursion, one must first understand recursion." Win XP & Office 2003 "LaserDude" wrote in message ... My spreadsheet contained an error: The equation for L4 in my original post referenced an incorrect cell. The exact formulae a 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 "JE McGimpsey" wrote: 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? In article , LaserDude wrote: 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 |
#6
|
|||
|
|||
Hi Peter. I was just curious on your equation, that's all. Earlier you
mentioned that. Iteration is enabled with 100 maximum iterations, and 0.1 maximum change. 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. -- Dana DeLouis Win XP & Office 2003 "LaserDude" wrote in message ... 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" wrote: 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 a 0.00076474810382905 and -0.00077691667356448 Hope I got that right. Are I4 & K4 variable inputs ?? -- Dana DeLouis "To understand recursion, one must first understand recursion." Win XP & Office 2003 "LaserDude" wrote in message ... My spreadsheet contained an error: The equation for L4 in my original post referenced an incorrect cell. The exact formulae a 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 "JE McGimpsey" wrote: 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? In article , LaserDude wrote: 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 |
#7
|
|||
|
|||
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" wrote: Hi Peter. I was just curious on your equation, that's all. Earlier you mentioned that. Iteration is enabled with 100 maximum iterations, and 0.1 maximum change. 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. -- Dana DeLouis Win XP & Office 2003 "LaserDude" wrote in message ... 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" wrote: 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 a 0.00076474810382905 and -0.00077691667356448 Hope I got that right. Are I4 & K4 variable inputs ?? -- Dana DeLouis "To understand recursion, one must first understand recursion." Win XP & Office 2003 "LaserDude" wrote in message ... My spreadsheet contained an error: The equation for L4 in my original post referenced an incorrect cell. The exact formulae a 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 "JE McGimpsey" wrote: 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? In article , LaserDude wrote: 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 |
#8
|
|||
|
|||
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 "LaserDude" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterate Circular Reference | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Worksheet Functions | |||
Help solve a Circular Reference | Excel Worksheet Functions | |||
Circular reference | Excel Discussion (Misc queries) |