This is a video preview of the Fourier transform model presented on this blog before.

A tutorial explaining the creation of such a model was posted here too.

Skip to content
# Excel Unusual

## Science, Engineering, Games in Excel

# A Basic Fourier Transform Calculator in Excel – video preview

##
This article has 36 Comments

### Leave a Reply

A tutorial explaining the creation of such a model was posted here too.

Anant, Fourier transform is a type of measurement, it shows the distribution of certain frequency components in a signal. In general any program program is limited, which means it can process a certain maximum number of points. Universe is however infinite. You cannot characterize the whole universe with a limited tool. You need to specify the start and stop frequency based on your needs. Let’s say you are looking at the power network in a residence (apartment). If you are a power engineer, you might be interested to look at anything between, say 10 Hz and 1000 Hz. If you are a telecom engineer you might look between say 100MHz and 2GHz since there might be telecom information transmitted over the same power grid. So, both engineers use the same tool and look at the same signal except they are interested to diagnose different regions of the spectrum. Another way to think about it, think at FT as a microscope. Specifying the start and stop frequency is like specifying the magnification and the orientation (where exactly the microscope is pointing) of the microscope. Or in a hospital, a foot doctor will use an X-ray machine to image the foot, whereas hand doctor will use the same machine to image the hand of the same patient. As far as Fourier transform is concerned, you need to start wide at the beginning of the measurement and after you identified certain regions of interests, you redo the measurements on certain narrow areas of the signal spectrum.

Hi George

Thanks for this excellent tool.

I have few queries though.

How do we decide what are the Start_Freq and Stop_Freq?

Not really. I should completely redo the whole Fourier section.

Is your DFT template equivalent to the Lanczos approach?

… To be more precise: I need to define with respect to the epitrochoid curve center its point vectors as a function of their vector angle for given base and rolling circle radii and k factor. I hope you can help.

Congrats George on your strong continuing support for Excel 2003. I am completely with you on the dumb down issue in newer releases. Anyway, I need to accurately define the direct polar coordinates of epitrochoid curve points with respect to the epitrochoid center with given base and rolling circle radii as well as k factor. All formulas I can find relate to the rotation angle of the moving circle.

I tried your fantastic Fourier Transform Calculator, but the results seem inconclusive. Since you are quite a math wizard I thought you might be able to help, please.

Hi Zhen! In this case I’ve chosen 3 different random frequencies (in that particular input function – however there are three input functions you can select) that happen to be the values mentioned by you. It’s an arbitrary choice just to prove that the Fourier calculator works OK. I could have chosen 1, or 7, or 25 different frequencies or any other number. On the other hand I have a better Fourier calculator version (built almost exclusively in VBA) and I will need to publish it on the site. Plus I will also have courses soon. Cheers!

Hello George, Where are the other frequency ? Why 1.5Hz,1.2Hz,0.9Hz are integrated in the function formula ?

I couldn’t resist commenting. Perfectly written!

Feel free to surf to my site … energy audit

When I say variable periods I mean create a mega series (2000 points) with a certain period between the 20 elementary series, then create more of these mega (fake) series with different periods. All these series are fairly close to each others. It’s more an optimization process using both visual clues and calculated clues.

My model tries to be a very basic implementation of a classic algorithm.

Try the following:

1. Plot the series and try to visually clean the ends (series of zeros?)

2. plot the about 100 points (less after cleanup) say, 20 times with variable periods and visually try to adjust the spacing to create a large series with a dominant fundamental freq.

3. Do the fourier calculations on the compound series and try to choose the one with the cleanest spectrum (largest power in the fundamental compared to the remaining spectrum). Keep finely adjusting the time distance between these 20 series until you reach a sweet spot. Use your eyes and common sense too. The tool is blind and should be used sparingly. People love black boxes but they rarely are the best solution. Also work on centering the series.

The camera is limited too 102 picture frames in each exposure, so i cannot create any more( i wish i could). Matlab was used to analyze the image and get an average pixel count for each frame. Which i then put into excel and plotted the change in pixel size over time. I have tried using your algorithm, but for example if i put the frequency range from 0 to 2MHz, there is a peak at 1MHz and spectrum is mirrored both sides, if i change it to 1 MHz, there is a peak at 500 Khz. Makes me think there is something wrong.

Here is the raw data for a single video: 102 points, dt= 2us. startings at 0s

The zeros represent no ultrasound on( Hence no cavitation bubble)

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 15 0 15 12 19 16 26 12 12 23 31 30 34 28 28 25 0 25 31 28 28 28 25 25 16 28 29 35 29 39 30 36 0 41 33 46 30 44 28 36 0 45 43 48 43 47 39 46 23 37 0 29 0 0 0 0 0 0 0 0

Does all the zeros affect the algorithm?

It appears from the raw data, that when the ultrasound is on and the bubble is at it minimum size, corresponds to pixel data at its minimum, but the bubble is oscillating to the frequency of the ultrasound, so the time difference between minimum is approximately every 16us(manually calculated from the graph), which refers to 62.5 KHz, so the bubble is oscillating at this frequency?

i imagined the FT would highlight this frequency?

Am i mistaken? Sorry for adding the detail, but i think this FT calculator is very useful, if i can get it working properly for this experiment.

Keith, “N” points is enough for a given precision. One advice, mirror the data (padd it with the same thing and multiply it by 2). Ultrasound is cheap, why not take more points?

Dear George,

I have 102 data points relating to the oscillation of a bubble inside a water medium when subject to ultrasound. The bubble was recorded using a high speed camera @ 500,000 FPS ( 2us between frames) with 102 frames and then a dark pixel algorithm detected the size and oscilllations of the bubble over 202 us time period. Each data point relates to a single camera frame, where it counts the number of pixels in the picture( The bubble appears white with the background black) If you plot the pixels over time you get an idea of the bubble oscillation, but i wanted to work out the frequency. Do you think 102 points is enough, i have used your software but it seems to be symetrical about the middle point, which makes me doubt it. I am not sure tho, what do you reckon?

I have 400 points

Paul, this is a digital approximation but not an FFT. You need to check and apply the formula for the FFT. Basically this particular model is not good for your application. How many points of data you have?

Hi George,

This technique i so useful but need your guidance.

Hi George,

I am new to this technique so it is very difficult to understand, but i have to do this because it is related to my research work i have two component to do this one is number of laminae and second one is thickness of laminae. So fist i have to do harmonic analysis than transform into fft. So pls tell your valuable guidance how i will run my data for harmonic analysis then transform to fft.

It will be great help for my research.

thankyou.

One more observation,the codomain of arcsin() would not work for (0,pi) anyway, I believe injectivity of sin() is violated. For instance if sin(x)=0.5 there are 2 different values of x in the interval (0,pi) satisfying that, therefore you cannot simply define the codomain or arcsin() as 0,pi. It has to be (-pi/2,pi/2), or (pi/2, 3pi/2), etc. So I was wrong, it is not purely a convention issue.

That helps a lot, thank you!

Grace, I will just give you some hints and I apologize if my language is not proper (might not in line with the US terminology). Go check in the middle school books about function properties. The way we learned them was: injectivity, surjectivity and bijectivity. As I remember correctly from 7th grade a function can have an inverse only on a domain on which it is bijective. By definition (and you need to check yourself in the books), the codomain of the arcsin(x) or asin(x) (same thing) is defined as (-pi/2,+pi/2) and not (0,pi). It’s just the definition. You need to go back and read about this. I am rusty myself and our system of education back in the day, in my old country was mainly based on the French/German style. And I cannot stress enough, the creators of these inverse trig, function decided as codomains for symmetrical intervals centered in origin. Of course if you like you can change the definition (by adding a pi/2) but then it will not be in line with international definitions.

George, awesome spreadsheet, your youtube video was really helpful. Thank you!

I have actually developed my own spreadsheet which is quite similar to yours, but I am having trouble calculating the phase shift correctly. When graphed against frequency, my shift values all lie between 1.57r and -1.57r (obviously pi/2 and -pi/2). Initially I thought my equations were wrong but I notice on youtube your phase values lie in this range as well.

When I use a simple sine wave, with no shift (sin(2pift)), as my input data, my fourier transform returns the phase shift (using atan(imaginary/real)) of approximately 1.57 (approaches the maximum). I get the same answer when using a shift of pi and a shift of 2pi in my input data.

Would you mind explaining if you could why there is negative shift instead of all positive beginning from zero, and why the shift only encompasses pi radians, not the full 2pi?

Any help would be much appreciated! Thanks!

Kelvin,

That’s something easy to prove even in the general case and if I do that I will be an obstacle between you and learning. Put your own effort in and don’t depend on others. You will do much better in life.

George,

do you mind showing me one example of fourier transfrom then inverse fourier transform to it’s original state?

I can’t quite understand how the constant of 1/2pi recover the fourier transform into its original state.

There are 1 million books but you can just google it and get free info. The inverse and the direct FT are the same except for a constant. I would start with Wikipedia and then continue with Google. Search for exercises too.

George,

It didn’t get approval, unfortunately. Would you mind explaining Inverse Fourier Transform in details ? or do you have any good sources for explaining Inverse Fourier Transform.

thanks

Try it out but make sure it’s approved by your instructor.

George,

I find out I can actually use IDFT and DFT function in Excel. I would just use that function.

Kelvin, I would use a built in Excel function if it were available but make sure to check with your pals in the class and see what they do first.

Hi George,

I think this assignment is to create a fairly simple calculator that can demonstrate with various time steps and frequency steps to compare accuracy of the function itself. The example from class is using random number generator instead of any real data from real life.

I have also downloaded your model and understand as much as I could but I think your model is beyond my assignment requirements. Deadlines are soon. I also found some useful videos from youtube. They used DFT and IDFT from excel.

if you were me what would you do to build a fairly simple calculator to demonstrate the time step and frequency steps ?

Kelvin,

The purpose of the class and the assignment is for you to do the research.

You can use Excel but the solution will be (sampled) which is approximate. I’m pretty sure that’s what they ask anyway.

My model is only for real functions, for the general complex case you need to develp the model further.

Use he formulas in the course and the model I built as an example to create a complex Fourier calculator. This might take you a few days or even a week. Don’t procrastinate. When is the final deadline?

George, thank you for getting back this quick. I actually having an assignment about inverse FT and I have no idea how to do this assignment.

If you don’t mind, can you please tell me what are the technique I need to use for the assignment requirements below. I believe I can do it with Excel.

Thanks.

Part A – Application of the Inverse Fourier transform

Using inverse Fourier transform techniques, build a calculator that can do the following:

Rebuild a signal once the Fourier transform is found

Demonstrate the effects of resolution of time, and frequency on the accuracy of the rebuilt signal

Kelvin, I have centralized comment control so I can see all the new comments on all the posts.

I’ve been slacking with the blog since we are fire evacuees right now.

I need to re update myself with the FT since it’s been more than two decades since I’ve studied this in school but to be brief the inverse FT uses the same formula as the FT and the difference is just a constant. In real life we only use the DFT since the normal (continuous FT) is calculated on an infinite number of points and on an infinite time (or space) domain, In DFT everything is finite and you only sample a small (perhaps thousands or so) of time and frequency points. FFT is DFT with a twist and it is designed for math processors to use minimum ammount of hardware/calculation time.

Hi George,

You might not read this in time since this excel is already 1 year old. I am sure you would know Inverse Fourier Transform as well.. Do you have a tutorial for Inverse Fourier Transform ? And one more thing, would you mind explain what is the different between Fourier Transform, Fast Fourier Transform , Discrete Fourier Transform and Inverse Fourier Transform.

I am very new to this topic so I have very minimum knowledge about them.

Thank you in advance.

Just click the green “Excel Download” button above. It works for me.

Where’s the Fourier analysis in excel download? It looks great!