Is it possible to generate transfer functions with Excel 2010 (or lower)?

Printable View

May 22nd 2011, 06:18 PM
MotM

Is it possible to generate transfer functions with Excel 2010 (or lower)?

This topic doesn't really fit well in any of the subsections of the forum so I figured this is the most appropriate place to put it.

If I have two random data sets of equal size (same number of elements) such as set X = (4,7,2,6,...) and set Y = (5,9,3,5,...) is there a way to use excel to find a transfer function between Y and X such that H(x)X(x) = Y(x)? In this case H(x) would be the transfer function which when multiplied by X(x) always produces Y(x) (i.e. X(1)H(1) = Y(1) = 5).

I know I could probably do this with MATLAB or some equivalent software, but I would ultimately have to port the transfer function over to Excel for my application thus it would be nice if I could just do this in Excel.

If there is no way to do this with Excel, please let me know of any other software you can think of that might be able to generate a transfer function of this nature.

cross posts of this in other forums:
http://www.excelforum.com/excel-gene...-or-lower.html
http://www.mrexcel.com/forum/showthr...=1#post2725426
May 23rd 2011, 03:33 AM
SpringFan25

Can you run an appropriate regression on the variable Y(x)/(X(x) and use this to estimate H(x)?
May 23rd 2011, 07:28 AM
MotM

Quote:

Originally Posted by SpringFan25

Can you run an appropriate regression on the variable Y(x)/(X(x) and use this to estimate H(x)?

Thats what I'm asking you. I don't even know where to begin on this. The only transfer function experience I have is with basic closed loop and open loop systems such as P, PI, and PID. I've never seen transfer functions for data sets like this. However, I don't see why it wouldn't be possible assuming the data sets were related in some mathematical way.
May 23rd 2011, 07:43 AM
Ackbeet

So you have your input vector X and your output vector Y. You can transform one vector to another vector of the same size most easily by left-multiplying the input vector X by a diagonal matrix H, where the entries on the main diagonal are defined as follows:

$H_{ii}=Y_{i}/X_{i}.$

Example:

$X=\begin{bmatrix}1\\2\end{bmatrix},\quad Y=\begin{bmatrix}3\\4\end{bmatrix}.$

You assume that there's a diagonal matrix H such that HX = Y. That is, there is a diagonal matrix

$H=\begin{bmatrix}h_{1} &0\\ 0 &h_{2}\end{bmatrix},$

such that

$\begin{bmatrix}h_{1} &0\\ 0 &h_{2}\end{bmatrix}\begin{bmatrix}1\\2 \end{bmatrix}=\begin{bmatrix}3\\4\end{bmatrix}.$

You can find the entries by simple component-wise division.

This may be a bit too bone-headed, but I think it's a valid approach, though it might need to be tweaked.