Revised - Easy FFT plotting
Revised - Easy FFT plotting
(OP)
Note - I have red-flagged the previous thread on this subject since the excel file posted there cause an error for Corus and also caused an error for me in Excel 2003. This new thread posts a revised verision which works for me in Excel 2003.
Attached is a spreadsheet which uses the Alglib FFT algorithm.
I have combined it with dynamic named formulas (similar to the ChartFmla spreadsheet posted in this forum) so that you don't need to enter any data or ranges... just enter the time waveform formula, number of points, and time spacing between samples and you get handy time waveform plot and spectrum plot. Does not require any tool-boxes or add-ins. Not limited to powers of 2 or below 4096... however it seems to give me errors above 10,000 points.
Attached is a spreadsheet which uses the Alglib FFT algorithm.
I have combined it with dynamic named formulas (similar to the ChartFmla spreadsheet posted in this forum) so that you don't need to enter any data or ranges... just enter the time waveform formula, number of points, and time spacing between samples and you get handy time waveform plot and spectrum plot. Does not require any tool-boxes or add-ins. Not limited to powers of 2 or below 4096... however it seems to give me errors above 10,000 points.
=====================================
(2B)+(2B)' ?





RE: Revised - Easy FFT plotting
RE: Revised - Easy FFT plotting
RE: Revised - Easy FFT plotting
I'll have a good look later.
Pete - what was the problem with the original version?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Revised - Easy FFT plotting
So I rebuit the file within excel 2003 and attached it to the first post in this thread... it runs fine in excel 2003 and excel 2000, but apparently not in excel 2007. I tried it on an excell 2007 computer and go the same error as before "automation error - catostrophic failure".
I was able to get into vba while the error is displayed. The line that is highlighted is the function header:
Function myfft(invar As Variant, Optional window As String = "none") As Double()
The function code is as follows (it calls some subroutines which I have not included
CODE
' Accepts time samples invar and returns spectral magnitudes using Alglib's FFT routine
' Requires Alglib FFT, FTBAS, AND AP modules
' INPUT: invar can be either a single-column range or a nx1 array of type double
' If input is range, convert it to nx1 array of type double
If TypeName(invar) = "Range" Then invar = invar.Value2
If UBound(invar, 2) <> 1 Then MsgBox ("Error - need one-colum input")
Dim Arm() As Double ' A - same as input, except type "Real Matrix" ... i.e. nx1 array of type double (2D)
Arm = va2rm(invar) ' store invar in Arm
Dim Avec() As Double ' Avec - same as A, except a vector of length n.l (1D... as required by Alglib's FFT routine
Avec = rm2vec(Arm) ' store Arm into Avec
Dim N As Long ' Number of points in input
N = UBound(Avec)
Dim ctr As Long
Dim windowscale As Double
Select Case window
Case Is = "none"
windowscale = 2 / N
Case Is = "hanning"
windowscale = 4 / N ' Not sure about this one
For ctr = 1 To N
'fnhanning(t)=(0.5 - 0.5*cos(2*pi*t/T2)
Avec(ctr) = Avec(ctr) * (0.5 - 0.5 * Cos(2 * Application.WorksheetFunction.pi() * ctr / (N)))
Next ctr
Case Else
MsgBox "error in window type"
End Select
' F will hold the output complex vector (1D array of complex) from the Alglib FFT routine
Dim F() As Complex ' Will return the output vector of the FFT routine
Call FFTR1D(ShiftIndexDoub(Avec, -1), N, F)
' f1 = same as F, with indeces shifted up by 1
Dim f1() As Complex
f1 = ShiftIndexComp(F, 1)
' f2 = same as f1, except will be stored as a nx1 array of type complex
Dim f2() As Complex
f2 = veccol2cm(f1)
' mags will hold the magnitudes
Dim mags() As Double
mags = cm_abs2rm(f2)
' Sale as required so that a sinusoid of peak magnitude 1 will show up as a 1 on the FFT output
mags = rm_MulR(mags, windowscale) ' SCALING
' mags(1, 1) = mags(1, 1) / 2 ' undo scaling of dc component in order to capture true dc component (even though it creates discontinuity in spectrum of heavily damped sinusoid)
' Scaling of dc component is tricky. Leave it scaled similar to others -> will give smooth spectrum
' output will hold the 1st-half portion of the magnitudes that are to be returned from the function
Dim output() As Double
output = rm_getsm(1, 1, Int(UBound(mags, 1) / 2), 1, mags)
myfft = output
End Function
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
Maybe I need to try to rebuild the spreadsheet again in excel 2007....
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Revised - Easy FFT plotting
Attached is a file that will load in excel 2007 but seems not to work in 2007
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
If you delete the block of formula's in B11:B20, then I think it works without error. What's wrong with those formula's?
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
Therefore:
use the file posted 24 Aug 10 21:39 for excel 2007.
use the file posted 24 Aug 10 11:43 for excel 2000, 2003.
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
Also if you declare pi as a constant at the top of the module it will save a lot of worksheetfunction calls, which should speed things up a bit (especially in 2007).
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Revised - Easy FFT plotting
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Revised - Easy FFT plotting
There is a far more aggregious time waster.... the FFT routine gets called a number of times. I also have the 3 cells that give a specific harmonic in cells B18:B20. I think that calls the FFT routine 3 more times (!).
It is still pretty darned fast. Recalculation time is barely noticeable. What is noticeable and somewhat annoying to me is that it takes about 5 seconds to save (and I like to save multiple revisions).
=====================================
(2B)+(2B)' ?
RE: Revised - Easy FFT plotting
I agree that the pi question is likely to be a side issue.
I think it might be worth hard coding one of the functions and getting rid of the Evaluate name as a trial though.
Also maybe limiting the number of points in the graph.
Anyway, time I was off to bed here :)
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/