This is the second half of the tutorial which shows how to build a basic animated 2D heat transfer model in Excel.
[sociallocker]
[/sociallocker]
Building a Dynamic Two Dimensional Heat Transfer Model – part #2
by George Lungu
– This is the second half of a tutorial which shows how to build a basic
dynamic heat conduction model of a square plate. The same principle could
be used to model different shapes 2D objects.
– The previous presentation explained how to partition the plate into
elementary square sections each having four neighboring elements and how
the heat transfer occurs between the elements by following the two rules
of heat transfer: the storage and the transport equation.
– This section explains the general idea about the worksheet layout and the
principle of matrix padding done in order to allow a consistent formula use
across the whole calculation matrix. This consistent formula application
makes for an easy and error proof model creation.
– This section also explains the implementation of the spreadsheet formulas
within the worksheet and the two macros used to animate the model.
by George Lungu
Excel implementation – worksheet layout:
– We already have the worksheet saved with the input parameters in place.
– In order to model a 30 x 30 heat conducting sheet need to create four 2D matrices in form
of Excel tables:
1. A calculation matrix (or current temperature matrix) having formulas for
calculating current temperatures. The formulas in this matrix will be based on data
from the following two matrices: the past matrix and the ambient temperature
matrix.
2. A past matrix or previous time step matrix. This matrix is a table of all the 30 x
30 temperatures of the body during the previous time step. A process of past matrix
padding will be described later. This is done in order to be able to use the same
formulas all across the calculation matrix (within the matrix, on the edge on the
matrix and in the corners of the matrix).
3. The ambient temperature matrix. These numbers are typically constants and
represent the ambient temperature map. The data in this table is used in the
calculation table formulas too.
4. The initial temperature matrix. These numbers are typically constants and
represent the initial temperature map. These numbers are used only for the first
iteration. The “Reset” macro will paste the content (values) of this matrix into the
past matrix where they will be used only once to calculate the temperature during
the first time step, after which the “Sart_Pause” macro will overwrite the past
matrix with the values resulted from the new calculations.
<excelunusual.com> 2
A worksheet layout and operation diagram:
n current n past int right past front past left past back past n past amb amb n n past
– We can see that the temperature calculations are recursive namely they are calculated from the
previous temperatures
– We can also see that once all the present temperatures are calculated, they can be used to
calculate the future temperatures (one time step ahead of present). This process can be automated
in a “copy-present-paste-in-the-past” loop which creates an advancing simulation time.
These are parameters used by the “CurrentCalculation Matrix”. We use buttons with The formulas in the “Current macros so that we can change the Calculation Matrix” have input
parameters in real time while the simulation Parameters data (arguments) from the “Past is running to make the model interactive. and Buttons Matrix” and the “Ambient Matrix”
The “Start_Pause” macro will copy the Current Calculation
This matrix has constant data (not temperature data into the “Past Matrix” during formulas) but the data is frequently every cycle of the internal “Do” loop. This allows
changed (every cycle of simulation).
for rolling of the simulation time since the array of
This is a data dump for the temperatures are calculated iteratively from past Past Matrix
Calculation Matrix”.
values. So as long as we the newly calculated
values are being constantly pasted in the past the
The “Reset” macro
simulation can go on indefinitely.
will copy the initial
Ambient Matrix
temperatures into
the “Past Matrix”
These are matrices of fixed content
Initial Temperature
(frozen constants), you can fill them
with data to your preference Matrix
<excelunusual.com> 3
Past matrix padding:
-We know from the previous presentation that a current element temperature is calculated
iteratively from the previous temperature of the same element and the previous temperature of
its direct neighbors. If the element is situated on an edge it will have only three immediate
neighbors instead of four and if situated in a corner the element will have only two immediate
neighbors (with a common side).
-We could use a different formula but that is not a good idea to have different functions
taking arguments from different relative #positions. This is more a matter of ease and
error proofing while building the model than anything else.
– We would rather like to have a single formula taking the same arguments from the We obtain this “Padded Past Matrix” by
same relative positions (immediate neighbors) using a “radial” equality padding (watch the
regardless of the position of the element blue rectangles to understand the rule filling
within the object (inside, on the edge or on the cells during the padding operation.
the corner). Also after typing the formula in a corner we would like to copy it unchanged across the matrix. This is easy and error
proof.
– To accomplish this we need to create one row of immediate neighbors wrapping around the shape on all four edges.
<excelunusual.com> 4
If we take the general formula applicable for a non-border element (having four neighbors):
The formula for the upper left corner whose element has only the right and the back
neighbor is:
By using the regular non-border formula in the upper left corner of the padded past
matrix we get the following (since Tfront_past = Tn_past and Tleft_past = Tn_past):
As a conclusion we can use a unique formula all over the current matrix by padding the
past matrix as shown in the previous page. Building the model this way is safe and easy.
Creating the matrices:
– The rows and columns in all the four matrices used in this Excel model are labeled between 1
and 30. The only role of this is to make the model more readable. We won’t spend any more
time with this feature or the border and color formatting for each table.
The Initial temperature matrix: The data for this matrix is in range [C151:AF180]. You can create
you own initial profile either using constants or using formulas. I used a parallelepiped in the
middle of a null field.
The ambient matrix: The data for this matrix is in range [C111:AF140]. You can create you own
initial profile either using constants or using formulas. I used a slanted plane.
<excelunusual.com> 5
The Past Matrix:
The data for this matrix is in range [C71:AF100]. You don’t need to fill this
matrix except for the padding: C70: “=C71” copy to the right up to cell AF70; AG71: “=AF71”
copy down to cell AG100; AF101: “=AF100” copy to the left up to cell C101; B100: “=C100”
copy up to cell B71;
The Current Calculation Matrix:
C31: “=C71+($B$9*(D71+C70+B71+C72-4*C71)+$B$11*(C111-C71))*$B$15/$B$13” then fill in the whole
range [C31:AF60] with the same formula by two copy-paste operations (on row and column).
The macros:
– The “Reset” macro will take the data from the Initial Temperature Matrix and paste it in the Past Matrix to make sure that the model starts at the right temperature profile. It will also reset the index value whose purpose is to let the user know in real time how many loop iterations have passed since the beginning of the simulation.
– The “Start_Pause” macro copies data from the Current Calculation Matrix into the Past Matrix and it does it in a loop.
Sub Start_Pause()
s = Not (s)
Do Until s = False
DoEvents
[B23] = [B23] + 1
[C71:AF100] = [C31:AF60].Value
DoEvents
Loop
End Sub
Since the current temperature is calculated iteratively from the past temperature values this copy-paste operation effectively advances the simulation time (after the spreadsheet finishes reevaluating the spreadsheet formulas) in steps equal to “h”.
Sub Reset()
[B23] = 0
[C71:AF100] = [C151:AF180].Value
End Sub
The chart:
Select the Past Matrix range [C71:AF100] => Insert => Chart => Surface.
– Why did we chose the Past Matrix and not the Current Calculation Matrix as the chart data?
We wanted the charted temperature surface to start exactly from the initial conditions not one
time-step later.
The end.
by George Lungu <excelunusual.com> 6
I forgot to explain D term.
-D*dC/dx represents salt still can disipate itself because of concentration difference. Salt will move from high concentration to low concentraion.
dM/dt=G*dC G=D*A/x just like your summary. I think it is amazing, basic ideas in different subjects are the same… 🙂
Give me some time Chang, I got a contract with a semiconductor firm and I will try to still produce some posts. Side work is a third priority. Let’s stay in touch on this comment page. I hope to make something for you this weekend. Work on it yourself and try to forget what you learned from the class (hard?). George
George, I have used iteration to solve the problem from PDF in Excel. What surprise me is the solution from iteration is more accurate than matrix operation.
Besides, different iteration methods has different total iteration numbers. Thank you very much. : )
You are welcome, Chang. Iterations and animation are the things that motivate me. I do this for fun and I do it only as long as it stays fun.
Unfortunately I will be out for few weeks to finish a contract. Thanks for commenting. Cheers, George
Hey, George. When I do back estimated “dT (m)= T(m)-T(m-1)” and rearranged the equation: I try to obtain Tn(m), so I write Tn(m) on the left hand side, but Tn(m) is a function of Tright(m), Tleft(m), Tfront(m), Tback(m) and Tn(m-1) which will lead to circular reference. What should I do to avoid this issue?
Chang, everything you said is true and everything I said is true too. They seem to contradict but in the context of numerical methods they are right at the same time. I am trying not to spoil your learning and that’s why I don’t want to give you a straight answer yet. Are numerical methods exact? If not what can you do to produce a back estimate? We are in an auto factory and I can tell you that for certain parts you can gently use a small wooden mallet to fit the parts. But if I catch you using it on some other components you are automatically fired. Here you need to use a mallet (hammer) on cartain terms otherwise they won’t fit. And using it will not affect the precision almost at all, (why?). Please confirm. George
Morning George, I sent you a email with attachment to explain my thinking. Hope this will not interrupt your work.
Usually I use matrix operation with code, but if I can use formulas in sheet it will be wonderful. I am interested in your learn method and trying to follow.
Chang, I will read it this afternoon and let you know.
Chang, about the back or forward estimate: in our problem we had two different types of estimates of temperature derivative, with respect to time (same place) and with respect to space (between the neighbors – same time). The backward derivative you can calculate the way I told you but you need to consider the neighbors in the PAST. It is a non critical approximation since for a small time step the gradient between neighbors does not change in time. You need to think about this a lot. About the space estimate in our problem we used backward estimate for the right and front neighbors and forward for the left and back neighbors. I am not sure about the last statement let me think a bit. The main idea is that the only thing that matter when you say forward or backward estimate in this problem is the temperature of the current (“n”) element. For the neighbors, don’t worry about them. They might be hot or cool and dandy but their evolution in time (a short time step) does not matter for the current element. All that matters is how hot or cold they are with respect to the central element. So the speed of temp. variation of the neeighbors is irrelevant. And that’s why you can take their temperature one or few time steps in the past if the time step is small. That’s why heat transfer problems are some of the easiest in physics to solve numerically. In electricity analogy, heat elements have no inductance just R and C.
Your problem from the PDF is not related too much with the heat transfer problem. I agree with the solution he gave you in the class. If you want me to put a twist I tell you the following: the second order equation you have there requires two initial conditions from the start. You have only one plus you have a final condition. A straightforward way of solving that is the way he did in the class. You can also take the final condition and convert it into an initial condition by starting with the initial point as given in the problem and assuming a second point (during the second time step) of zero value to start with. Use an error function at the last point and a quick run (10-20 iterations) to minimize the error while you are acting to the second point. It is essentially a feedback system and you can get rid of the matrices (I hate them too). What’s the real life application of the problem? I might get some motivation if I know it is useful for something.
The equation in my PDF has a little real application. The completed form of the equation comes from mass conservation which just like what you did for heat problem. For example, there is a straight pipe in which water flows with velosity of U. One end of the pipe connects to a reservoir which contain clean water. The other end of pipe connects to salt water. Always keep the one end with salt water and the other end with clean water.
So, the problem is how is the distribution of salt concentration in the pipe?
The basic method is just same as what you did but in a control volume(A*dx):
mass changed in volume=mass in – mass out + source – decay
We can assume no source and decay
mass in: C*U*A*dt(advection term)+ -D*dC/dx(diffusion term) at x
mass out: C*U*A*dt(advection term)+ -D*dC/dx(diffusion term) at x+dx
mass change in volume: d(C*V)
so we can get dC/dt=-U*dc/dx+D*d^2C/dx^2
if we assume steady state which means concentration will not change with time at this status, then dC/dt is 0.
if we let U =1 and D=0.1 then the equation will be dC/dx-0.1*d^2C/dx^2=0
we know boundary conditions for two ends because one end keep salt concentration with 1 and the other end keep salt concentration with 0.
Here is the whole problem, I don’t know if my english can make you understood this, so sorry for that. I want to discuss numerical approximation with you, hope I will not let you feel boring…Thank you very much.
George, I have practiced this 2D model. Your method is really unbelievable, thanks a lot.
I have a question. As for governing formulas, it seems that excel can only calculate current value using forward estimate. If we want to calculate using back estimate, excel cannot do that, right?
Sure you can, no problem. You can do forward, backward or central or any combination, say double (time step) backward, double backward or central etc etc, whatever you imagine BUT you need to work out the formula by hand first. I personally picked up the easiest (low hanging fruit – the smallest possible denominator) – forward (time saving). Just go in the long continuous time formula, estimate the differential the way you like (backward) and then extract the latest term (Tn+1) Now the formula will have a huge denominator and a small numerator. Your final Tn+1 will be the current and the Tn will be the previous to current.
You can check out my spring-mass-damper system tutorials or RC filter tutorials where I use both. But I advise that you read very little (1-2 minutes at the most) and then make your own to learn. At your age (any age actually) a high reading time to experimention time ratio is detrimental to the brain. That’s what the school does and delivers babies to the industry. The whole industry is run by baby-brained people who would never venture in the forest alone at night unless they have large software packages and can hold the hand of a large team of experts fed by a large budget and management team. In the future I will do an RLC where I also use the central too. There you have double derivatives too (which are easy to be central).
What I like about excel is that it can do what you do with a pencil and paper, just much more productively. There is no: Excel can do this but it cannot do that. Yes Excel cannot do game quality graphics, but it can calculate any problem of reasonable complexity under the sun. Anything. Actually you solve it in your mind and excel calculates the solution. The VBA, animation etc are smoke and mirrors and used for the show to keep yourself infatuated with the problem. Cheers, George