Lets say you have your input data in A1:A32 and your output data in
B1:B32

In cell C1 you put
=IMABS(B1)

In cell D1 you put
=IMARGUMENT(B1)

Copy C1:D1 down thru C32:D32

In cell E1 you put
=1/32*SUMPRODUCT(C$1:C$32*COS((ROW()-1)*(ROW($1:$32)-1)/16*PI()+(D$1:D$32)))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER

Copy cell E1 down thru E32, and copy it further down ot get your
projection of future sales.

Here is an example with a "square wave" input.

A1:A8 = 1, A9:A16 = 0, A17:A24 = 1, A25:A32 = 0

You will get the following Fourier components
B1 = 16
B2 = 0
B3 = 2-10.05...i
B4,B5.B6 = 0
B7 = 2-2.993...i
B8, B9, B10 = 0
B11 = 2-1.336...i
B12, B13, B14 = 0
B15 = 2-0.397...i
B16 = 0
B17 = 0
B18-B32 are same as B16:B2 but with the opposite sign of the imaginary
part (the complex conjugate)

You will get the following amplitudes:
C1 = 16
C3 = 10.25166
C7 = 3.599905
C11 = 2.40538
C15 = 2.039182
C2, C4, C5, C6, C8, C9, C10, C12, C13, C14, C16, C17 = 0
C18:C32 are same as C16:C2

You will get the following "phases":
D3 = -1.37445
D7 = -0.98175
D11 = -0.58905
D15 = -0.19635
D1, D2, D4, D5, D6, D8, D9, D10, D12, D13, D14, D16, D17 = 0
D18:D32 are the same as D16:D2

In column E, the restored data, you will get the same as in
column A except some minor rounding errors. If not, check the
formulas.

If you have all this setup and checked you can change your input data
in A1:A32 and make a new Fourier transform to get the data in B1:B32.

Hope this helps / Lars-Åke



On Sat, 17 Jan 2009 14:06:01 -0800, MichaelRobert
wrote:

Lars-Ake:

Thanks for the ideas. How/where would I apply the IMABS and IMARGUMENT
functions?

Mike

"Lars-Åke Aspelin" wrote:

On Sat, 17 Jan 2009 07:01:00 -0800, MichaelRobert
wrote:

I want to analyze 36 months of historical sales figures to see if I can
identify any time-series that will allow me to project them into the future.

My rusty math memory tells me that I can use Fourier Analysis to find out
the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus
what I have read on the web - tells me that the number of data points that I
use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months
of data (I use 32). I have also learned that the frequencies reported by the
Add-in are listed in ascending multiples of 1/(32 months). From this I can
calculate the value of each frequency returned by the Add-in.

What I don't yet know how to do with the output from Fourier Analysis is:
1. How do I interpret the number reported for each frequency? I assume that
each one is a measure of the amplitude of the reported frequency, but I do
not know how to handle the 'real' and 'imaginary' components of the number.
At first blush, I think I would ignore the imaginary, but ...
2. Once I have the frequencies and the amplitudes of each component
waveform, I assume that I select to use only the more significant (higher
amplitude) frequencies. Help on this selection process will be appreciated.
3. To make my projection, how do I make sure that I am applying the
waveforms at the correct part of their cycle. For example, I could start all
of the waveforms at t=0, but that is not necessarily where the cycle should
start.

Overall, it seems that I am looking for the equation for the full waveform
that Fourier Analysis is calculating; so that if I insert the output of the
FA into it, I will get the equation for the projected waveform.

Thanks for your help.

Mike

For the amplitude you have to use both the real and the imaginary
part.
Use the IMABS function for this

For the phase you also have to use both the real and the imaginary
part.
Use the IMARGUMENT function for this

The more of the component that you use, the more accurate/detailed the
projection will be. But do remember that future always bring in new
facts, in addition to historical data, that will influence your
sales.

Hope this helps / Lars-Åke