sine wave in excel

mufc802 · 22 Dec 2010 at 23:26

I'm learning about sine, cosine and tangent waves so thought I would make some in Excel, so I typed in the angles from 0 to 360 in 15 degrees intervals. To give the sin values I used the formula =SIN(ANGLE*PI()/180) which works. I'm not sure why on 360, and in the cycles after so 720 degrees it comes up with -2.4503E-16 for 360 and -4.90059E-16 for 720 instead of 0. Also for 180 it says 1.22515E-16 instead of 0?

Dunks · 22 Dec 2010 at 23:27

Excels level of numerical accuracy. Those numbers are incredibly small.

flibbage0 · 22 Dec 2010 at 23:29

Format the cell so it only shows certain number of sig figs or decimal points?

muon · 22 Dec 2010 at 23:29

------

nydryl · 22 Dec 2010 at 23:30

Wolfram Alpha might interest you if you're looking at these functions.

mufc802 · 22 Dec 2010 at 23:32

flibbage0 said:
Format the cell so it only shows certain number of sig figs or decimal points?

Oops forgot to do that, it was set to general instead of number so no wonder it didn't make sense. Cheers.

ordinaryjoe · 23 Dec 2010 at 01:49

Yateszy said:
Oops forgot to do that, it was set to general instead of number so no wonder it didn't make sense. Cheers.

sorry if this is really stupidly obvious but you do know that the E means x10^ don't you? i.e. 2.5E-6 means 2.5x10^-6, so anything to the power of -16 is less than 1 millionth of a billionth

mufc802 · 23 Dec 2010 at 11:36

ordinaryjoe said:
sorry if this is really stupidly obvious but you do know that the E means x10^ don't you? i.e. 2.5E-6 means 2.5x10^-6, so anything to the power of -16 is less than 1 millionth of a billionth

I guessed it meant something like that but not sure why it put that instead of 0.

Anyway, I'm puzzled about something else now..

The formula I used for the blue line is =2*SIN(ANGLE*PI()/180)+3*COS(ANGLE*PI()/180), so say the angle was 10, it would give an answer of 3.3017. What I want to find is the exact angle(s) for 3.5, so how do I reverse the formula to find them as there should be two where the line crosses the y axis. I could take a rough guess of 20 and 46 degrees looking at the graph but is there a way to get the exact answer using a formula?
Also, how do I find the max amplitude the line reaches by using some sort of formula instead of just looking at the graph?

physichull · 23 Dec 2010 at 11:56

Yateszy said:
I guessed it meant something like that but not sure why it put that instead of 0.

Anyway, I'm puzzled about something else now..

The formula I used for the blue line is =2*SIN(ANGLE*PI()/180)+3*COS(ANGLE*PI()/180), so say the angle was 10, it would give an answer of 3.3017. What I want to find is the exact angle(s) for 3.5, so how do I reverse the formula to find them as there should be two where the line crosses the y axis. I could take a rough guess of 20 and 46 degrees looking at the graph but is there a way to get the exact answer using a formula?
Also, how do I find the max amplitude the line reaches by using some sort of formula instead of just looking at the graph?

You can cheat using Excel's Goalseek function for the 3.5 question, you can also use a couple of IF statments to find the max, alternatively use use Excel to use Conditional Formatting to highlight the max value.

If this is project work or whatever, then Goalseek is very much cheating and you would get no marks!

nydryl · 23 Dec 2010 at 11:59

A genuine question but why do people do this sort of thing in excel? It seems quite complicated.

mufc802 · 23 Dec 2010 at 12:01

physichull said:
You can cheat using Excel's Goalseek function for the 3.5 question, you can also use a couple of IF statments to find the max, alternatively use use Excel to use Conditional Formatting to highlight the max value.

If this is project work or whatever, then Goalseek is very much cheating!

No it's not a project it's just from a textbook I'm learning about waves. I've heard of Goalseek but can't remember how to use it, and how do the IF statements work to find the highest value on the graph and what's Conditional Formatting?

In the book it answers it by just saying just looking at the plotted graph and saying a rough guess for both answers. But how do you reverse the formula =2*SIN(10*PI()/180)+3*COS(10*PI()/180) which makes 3.3017, so reverse 3.5 to find the angles?

Dr Delphi · 23 Dec 2010 at 12:04

nydryl said:
A genuine question but why do people do this sort of thing in excel? It seems quite complicated.

If you want hard you should see what I teach my undergrads in physics, they do Monte Carlo and numerical differentiation and integration.

As to your other question OP, if you want to find the maximum or minimum of a function (and want to do it through algebra) then take the differential of the function (baring in mind this is the gradient at a specific point), set it equal to zero and solve (the zero point will be the plateau). To find out whether thats a maximum or minimum you can take the second differential for that point, and see whether it is positive or negative (iirc positive is minimum, and negative is maximum).

physichull · 23 Dec 2010 at 12:22

Delphi-uk said:
If you want hard you should see what I teach my undergrads in physics, they do Monte Carlo and numerical differentiation and integration.

As to your other question OP, if you want to find the maximum or minimum of a function (and want to do it through algebra) then take the differential of the function (baring in mind this is the gradient at a specific point), set it equal to zero and solve (the zero point will be the plateau). To find out whether thats a maximum or minimum you can take the second differential for that point, and see whether it is positive or negative (iirc positive is minimum, and negative is maximum).

I wish I could remember how to do basic maths, I use Monte Carlo codes almost every day at work to solve the Boltzmann Radiation Transport equation (I'm a radiation shielder) but I really can't remember basic maths anymore! I used to know so much during uni, but all that ability just drops right off when you leave and don't use it!

Pumpkinstew · 23 Dec 2010 at 12:29

Yateszy said:
I guessed it meant something like that but not sure why it put that instead of 0.

It's because of a very small rounding error introduced by your use of Pi (which can't be expressed as a decimal) in Excel. If you put enough significant figures in you'd probably find all of your values are marginally out.

Any reason why you want to use degrees instead of radians? This would remove the conversion factor you've put in and 2pi should be equal to zero again.

Dr Delphi · 23 Dec 2010 at 12:34

physichull said:
I wish I could remember how to do basic maths, I use Monte Carlo codes almost every day at work to solve the Boltzmann Radiation Transport equation (I'm a radiation shielder) but I really can't remember basic maths anymore! I used to know so much during uni, but all that ability just drops right off when you leave and don't use it!

Basic maths is simple - I find the middling maths the most abstract, coming back to the Schr\"{o}dinger (excuse the LaTeX) equation after three years of using it, and writing about hamiltonians and operators and the like is killing me!

I went for a job as a radiation shielder when I finished my Masters, was asked whats kinds of radiation there were, could only answer the simple three (forgot neutron) needless to say they didn't hire me

Monte Carlo really is a useful algorithm though!

physichull · 23 Dec 2010 at 12:41

Delphi-uk said:
I went for a job as a radiation shielder when I finished my Masters, was asked whats kinds of radiation there were, could only answer the simple three (forgot neutron) needless to say they didn't hire me Monte Carlo really is a useful algorithm though!

Oh really? Which company?

Remember, in the "real world", there are only two types of radiation, "Good Radiation" and "Bad Radiation"! That's what my mentor teaches me everyday in the industry. He's damned right too!

nydryl · 23 Dec 2010 at 13:30

Delphi-uk said:
If you want hard you should see what I teach my undergrads in physics, they do Monte Carlo and numerical differentiation and integration.

It's not that the material is particularly difficult it's why the op is using Excel that I don't get. Is it actually good for this sort of thing? Surely Mathematica and god forbid even Maple would be better?

physichull · 23 Dec 2010 at 13:33

nydryl said:
It's not that the material is particularly difficult it's why the op is using Excel that I don't get. Is it actually good for this sort of thing? Surely Mathematica and god forbid even Maple would be better?

Excel can be a brilliant tool for what I do at work, handling and processing large amounts of numerical data etc. Excel is one of those things that has more capability than you think, its just you have to find out what it can do as it doesn't tell you!

nydryl · 23 Dec 2010 at 13:36

physichull said:
Excel can be a brilliant tool for what I do at work, handling and processing large amounts of numerical data etc. Excel is one of those things that has more capability than you think, its just you have to find out what it can do as it doesn't tell you!

That's quite interesting I only ever thought of it as a spreadsheet that can do basic maths. Didn't know it had more capabilities.