Playing with Waves in Excel

[Armagdn03]

Here are a few tips for playing with waves in Excel,

This was a post I found while looking for excel graphics to cover this topic, it actually states how to create sine waves very well....


"
If you got excel, and know how to use it a bit for graphing, then you can definitely try that. It's not too bad at all.

What you can do is to generate a whole bunch of numbers in column A .....these will be for your angles. Maybe in column A, you might want the angles in degrees. Like make a column that has numbers that starts from zero, and goes up by increments of 0.2 degrees.

So you do this by putting 0 in cell A1, 0.2 in cell A2, 0.4 in cell A3 .... you could keep going, but there's no need. Then you can use the trick of highlighting cells A1, A2, and A3. And when they're highlighted, let go of the mouse button, and then put the mouse cursor on the bottom-right hand side of the highlighted part......put it exactly at the right-hand bottom corner of the highlighted block. The cursor will change into a black PLUS sign. Then push down the left mouse button and keep it pushed in. Then scroll the mouse downwards until the column numbers themselves start scrolling and building up. After a while, the scrolling columns will start getting some momentum and pick up speed. The further down you scroll your mouse, the faster the the column will scroll! And if you let go of the mouse button, a big column of numbers will be generated ....spaced at intervals of 0.2

If you generate numbers up to row number 4001, then the highest value we'll get is 800 degrees.

Then, for column B, you'll need to convert the column A values to RADIANS ....this is because excel's SIN function needs the angle to be in radians. So just use the equation: angle in radians = angle in degrees * 2 * PI / 360

So cell B1 should be: =SUM( A1 * 2 * pi() / 360 )
and cell B2 should be: =SUM(A2 * 2 * pi() /360 )
etc.

And then you can generate the results of the SIN function in column C.

So cell C1 should be: =SIN(B1)
cell C2 should be: =SIN(B2)
etc

Then once you're done, you simply hold down the CTRL key and keep it held down, then use the mouse to click on the 'A' at the top of column A, and while not letting go of the CTRL key, you can click on the 'C' at the top of column C. This will highlight column A and C.

Then go to the menu to find 'INSERT' .... so you can insert a CHART. Select 'XY SCATTER'....and bob's your uncle. The units for the x-axis will be 'degrees'.

If you want to add another plot....like y = 3*SIN(x), then you can use column D.....
cell D1 will be: =3*SIN(A1)
cell D2 will be: =3*SIN(A2)
etc.

You can delete the first graph. Then just use the same procedure as before...but this time, hold down the CTRL key, and click on the 'A' at the top of column A, click on the 'C', and click on the 'D'. So you get column A, C, and D highlighted. Then just INSERT -> CHART -> XY-SCATTER ...... and then you get two plots on the one graph.

If you don't want the legend to say 'series 1 etc', you can change the names of the series by right-clicking on the graph....and a menu box comes out....and you can select 'source data' to give new names for the series. Would have been more intuitive to change the series names by right-clicking on the legend itself though.....but excel doesn't work like that."


I would like to add, that you do not need to do the radians equation to convert, excel has a function to do this

type in "=radians(angle in degrees)"
and you will skip the 2*pi/360 equation

I tried to enter a spread sheet in the attachments, however it does not recognize excel spreadsheets, If you want I can send anyone interested an email containing this spreadsheet.

[Armagdn03]

Now that you can create one sine wave, you can also modify its frequency!


Essentially all you will do is this...

= sin((2*pi/360)*angle*frequency)

Previously we had only

sin((2*pi/360)*angle) - this automatically sets the frequency to 1hz or one cycle per 360 degrees. All we have done is insert frequency into the equation

Now you can modify your frequency to whatever you want....however take note that if you have a very fast frequency, with few "steps" in your list of angles you may get a very screwy graph, for example,


If you have 2hz and a list of 1,2,3,4...359,360 you will have a full 360 data points to mark your 2hz wave,

however if you graph 1,000,000 hz with only 360 data points, you can imagine its inaccuracy! so remember, have many more data points or angles than you have Hz.

[Armagdn03]

Now that you have One sine wave, with which you can change the frequency, you can move onto adding two frequencies together:

take your column with your angle, your column with your radians and your column with your =sin((2*pi/360*angle*frequency) and copy, then paste into an empty space in the spreadsheet

Now you will have two columns of =sin((2*pi/360*angle*frequency). The numbers in each of these columns represents your sine wave values which are ploted out to create your wave.

Depending on what you put in for the frequency, they will look different!


Now you can create a totally new column, which takes the awnsers from the first =sin((2*pi/360*angle*frequency) column, and adds them to the awnsers of the second =sin((2*pi/360*angle*frequency) column.

The addition of these two columns gives you a sumation of the two waves..If you have the same freq in both, you will have a wave of double amplitude! If they have a frequency difference between them, they will add together in some points causing increased amplitude, and subtract in others, causing decreased amplitude, this creates a BEAT frequency

The frequency of this beat is the absolute value of F1-F2

you may add as many of these together as you like....which is what I have done in the thread for the Missing Fundamental Generator

http://www.energeticforum.com/renewa...html#post96357

[Harvey]

Great Stuff!

Just a few things I use that others may find helpful:

1. Using Absolute referencing.
You can put your desired frequency in a single cell and refer to it in all the other cells. This allows you change one cell easily to update the graph to that frequency.

Lets say your column C has the above equation =sin((2*(pi()/360)*angle*frequency) where 'angle' refers to column A. You will notice if you copy that column to column D all the references automatically change to refer 'angle' to column B, an undesired effect. The cell identifier will look something like A10 etc, and when you past, it will change to B10 etc. Place a $ in front of any part of the reference you want to remain fixed. So by using =sin((2*(pi()/360)*$A10*frequency) and copying that single cell to the entire column D, it will update all of the row numbers while keeping the $A reference regardless of how many columns you copy it to. Likewise, the 'frequency' can refer to a specific cell. You can insert a row at the top of your spreadsheet and use the topmost cell in each column as a frequency for that column. In this case, for Column D for instance, the new formula would be =sin((2*(pi()/360)*$A10*$D$1) or sin((2*Radians($A10)*$D$1). You then put a single frequency number in cell D1 and all of the cells under will use that single cell. Placing your cursor inside a cell reference and pressing F4 on the keyboard will automatically insert dollar signs for you. Press it repeatedly to cycle through the 4 options: Row Only, Column Only, Both, None.

2. Smoothing the X-Y Scatter graph:
There are 5 different sub-types of X-Y scatter. Points only, Points with Smooth Lines, Smooth Lines only, Points with Straight Lines, Straight Lines only. By using either of the Smooth sub-types you don't need as many points to get the average waveform, but you do want at least one point at each of the peaks. Try entering some peak data such as 1 and 5 over and over in each row and then graph it. Beautiful isn't it?

3. Getting your repeating sequence without scrolling the mouse:
Place your starting number in the first cell, say A1 and click on that cell. Now select menu option "Edit" and select "Fill" and select "Series". A small dialog box will pop up which lets you set the fill to "Column", "Linear" and your step value (enter 0.2 for your example) and your stop value (enter 360 for 1 full cycle of degrees) . Click OK and Presto, your precision list is created.

4. Jump from the top to the bottom of a list and highlighting:
If your cursor is in a cell in your column and you would like to go to the bottom of the list, simply hold down the CTRL key and tap the down arrow key. This technique move the cursor to the last populated cell before a blank cell. Any of the arrow keys work this way. This is very useful when you want to copy just a portion of the data from a column and not the entire column itself. By holding down both the Shift and the CTRL keys, everything between the current cursor and the end of its travel will become highlighted. Then you can use CTRL-C, CTRL-INSERT, EDIT COPY or Right click the mouse and select COPY - any of these will copy the highlighted data to the clipboard. Personally, I use CTRL INSERT to copy and SHIFT INSERT to paste because I can do that with one hand on the keyboard. While this works in most windows programs, the industry standard is actually CTRL-C and CTRL-V.

EDIT: I just realized while working in a spreadsheet that I use another speed trick without even thinking about it. This accomplishes the same thing but by a series of keypresses. Hold down the 'Shift' key and tap the 'END' key, and then an 'arrow' key. The cursor will move to the end of the populated cells highlighting them as it goes.

Cheers!



ETA: To upload a spreadsheet as an attachment you can rename the file and add '.doc' to the end. Simply instruct the users to remove those last four characters prior to opening the file. (just noticed there is a 97KB limit for this )