Help: Excel's Fourier analysis

[sulphox]

Hi all,

I tried to perform Fourier analysis in excel (under Tools>Data analysis>Fourier analysis) and I got the results that I want.

However, I encountered a problem when I need to plot the graph of the Fourier analysis.

For instance,
I need to plot "t" against "the magnitude of the Fourier analysis"

If Fourier analysis of y(t) is a+ib (where a is a real value, b is a complex value) the magnitude of Fourier analysis is a^2+b^2

Therefore, what I want to ask is that, is there any way I can split the 'a' term and the 'b' term into separate column in excel(besides manually splitting them, because I have a lot of input values)? So that I can find the magnitude easily.

Below are the examples for the scenario that I’ve drawn.
t=-1, -0.9, -0.8, etc
y(t)= rect (t) -> y=0, 0, 0, etc
Fourier analysis of y(t) -> 33, -20.2574504846576-1.99518472667218i, -0.980785280403231-0.195090322016127i, etc (where the real value and complex value under same column)

So is there any way to group all the real number under 1 column i.e.
33, -20.2574504846576, 0.980785280403231

While -1.99518472667218, -0.195090322016127 under the other column.


I am sorry, perhaps the description of my problem seems complex, nevertheless I hope u guys can understand.

Thanks for any help in advance.

Sulphox

 

[Tazguy37]

Are you looking for something like this?:

Book1
	A	B	C	D	E
1	Original vector	Real comp.	Imaginary comp.	Imag. Comp. Magnitude	magnitude
2	5+2i	5	2i	2	5.385164807
3	-3-i	-3	i	1	3.16227766
4	-2+1.5i	-2	1.5i	1.5	2.5
5	2.5-0.8i	2.5	0.8i	0.8	2.62488095
6	1+i	1	i	1	1.414213562
7	1-i	1	i	1	1.414213562
Sheet3

Formulas:
B2: =LEFT(A2,IF(ISERROR(FIND("+",A2,2)),FIND("-",A2,2),FIND("+",A2,2))-1)
C2: =RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("+",A2,2)),FIND("-",A2,2),FIND("+",A2,2)))
D2: =IF(NOT(ISNUMBER(--LEFT(C2,LEN(C2)-1))),1,LEFT(C2,LEN(C2)-1))
E2: =SQRT(B2*B2+D2*D2)

and copy the formulas down the column. Is that what you're after?

 

[RalphA]

Before I saw TazGuy's solution, I came up with the following one, but posted later than TazGuy. Sorry!

In cell A1, enter "Complex Number"; in cell B1, enter "Real Part"; in cell C1, enter, "Imaginary Part".

In cell A2, enter the first complex number, and expand that column to accomodate this number.

In cell B2, enter =MID(A2,1,FIND("-",A2,5)-1), and expand that column appropriately. You should see the real part of the complex number there.

In cell C2, enter =MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2)-1), and expand appropriately. You should have the imaginary part of the complex number, without the i at the end, without the ending "i". For the ending "i" to be included, use =MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2)).

Now, you can copy down the two formulas, for the rest of the numbers.

 

[Ronald Moore]

Take a look at the IMABS function in the Analysis ToolPak.

 

[Tazguy37]

Good call, Ronald!

Here's a small screen sample using said engineering functions (remember that they do require the Analysis ToolPak Add-in):

Book1
	A	B	C	D
1		Imag.Coef.	Real Coef.	Magnitude
2	1+i	1	1	1.414214
3	1-i	-1	1	1.414214
4	-1+i	1	-1	1.414214
5	-1-i	-1	-1	1.414214
6	2+3i	3	2	3.605551
7	2-2i	-2	2	2.828427
8	-1+2i	2	-1	2.236068
9	-1-2i	-2	-1	2.236068
Sheet1

Formulas:
B2: =IMAGINARY(A2)
C2: =IMREAL(A2)
D2: =IMABS(A2)

and copy down the column. HTH

 

[Zack Barresse]

Please make a note when cross-posting: http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1133664897

 

[sulphox]

Hi all,

Thanks for all your help.

I think I'll just follow Ronald's method will be easier.

By the way, TazGuy37, you gotta consider the positive and negative sign for the imaginary component.

Thanks once again guys.

 

[Tazguy37]

By the way, TazGuy37, you gotta consider the positive and negative sign for the imaginary component.

True enough. Good thing it really doesn't matter if you're finding the magnitude of the resultant vector.