Neat Spreadsheet Feature : Matrix Inversion
Matrix inversion is one of those things that you hoped you would never see again after your linear algebra course (if you had one ... I never did). But it's a
fast path to solving for node temperatures in a thermal network. Here's how:
First, write the governing equations in the form Q=XT, where Q
is a vector of applied power, X is a matrix of coefficients describing the resistances in the network, and T
is a vector of node temperature rises. (You get the matrix by writing heat balances at each node; treat ambient temperature as zero to reduce the number of nodes.) Set up the X matrix in the spreadsheet with values at each position. For
n temperature nodes, you should have n heat balances, so the matrix will be n x n. Also set up the forcing-function vector Q, which should have n
terms -- some of which will be zero for the nodes at which there are no heat sources.
Then, let the spreadsheet solve for the temperature rise vector. T=(XtX)-1X
tQ is written in Excel using the built-in linear algebra functions: MMULT(matrix1,matrix2), MINVERSE(matrix1), and TRANSPOSE(matrix1).
To implement this, you'll need to use an array formula. Select a vector range that is one column wide and n
rows tall. Then type =MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(Xmatrix),Xmatrix)),TRAN SPOSE(Xmatrix)),Qvector) (I hope that's right!). Instead of hitting Enter, use CTRL+SHIFT+Enter to make it an array formula. The same formula gets entered in each cell of the vector you originally selected. Voila! A vector of temperature rises. Then, add the ambient temperature to each to get a node temperature.
If you've done everything right, the heat flows at each node should sum to zero, a good sanity check in any case.