Excel and SIN: We have a problem

Factual Questions
1

Mrs. RickJay and I are trying to get Excel to graph a sinusoidal function. We simply cannot get it to calculate the right values.

Here’s what we’re doing; in one column we have time intervals from 1 to 50, 1,2,3,4 and so on. In Column B we are attempting to find the sinusoid of a 1200 Hz frequency with no phase shift. Now, according to every damned formula I can find, this is the formula:

=SIN(2*PI()1200T)

Now, unless I’m crazy, what this formula should do is calculate 2 times PI times 1200 times the time (which starts at 1, 2, 3, etc.) and then take the sine of that number, right?

Well, now, I always thought the sine of any positive number was a value between -1 and 1, right? And that’s what this should do. 2 times PI times 1200 times, say, 1 (for the first time interval) is 7539.822359 or something like that. And the sine of that is -.349. With me so far?

Okay, no problem. So we typed that into an Excel cell. And it gave us…

-407733E-13

Uh… WHAT??? What the hell’s that?

I’ve gone over the formula again and again and again. Mrs. RickJay has gone over it again, and again, and again. The formula’s typed in right. If I take out the SIN function it gives me 7539, which is right. If I do SIN(7539.822) it gives me -0.349, which is also right. But if I try SIN(function) it simply will not work. In fact, if we calculate (2*PI()1200time) in one cell and do =SIN(cell reference) in another, you get 7539.822 in the first cell, but it still does not work in the second cell. Why? What are we doing wrong?

2

Rick, I think your problem is that you’re using a different measure for angles than Excel is.

If I take the sin of 2400[symbol]p[/symbol] degrees, I get -0.349. If I take the sin of 2400[symbol]p[/symbol] radians, I get zero (or, in essence, what you got). Try converting from radians to degrees first.

3

Of course, that ignores that it gave you SIN(7539.822) correctly, doesn’t it? But strangely, when I ask Excel for sin(7539.822), I get -0.000369.

What you’ll want to do, I think, is to use smaller time steps. With a frequency of 1200 Hz, you should have 1200 oscillations in 1 second anyway. Try increasing t by 0.0001 instead. If you do, you get something that looks nicely sinusoidal. So your function is correct, but don’t use t = 0, 1, 2, 3, etc.

4

Thanks, gr8guy. But we’re still confused; where are radians in the formula I provided? (If this sounds like a stupid question you should see the notes we’re working off.)

The values inside the brackets are

A time number that increases from 1 to 50, so in Cell B1 it’s just 1

  • The frequency 1200 - just the number 1200

  • The number 2

  • The number PI

Note that if I take out “PI” and replace it with “3.14159” I get totally new and fascinating numbers.

Perhaps there’s a trick in Excel I do not understand; for instance, using my scientific calculator, the SIN of 7539.816 is: -.345.

But if I type in =SIN(7539.816) into a cell in Excel, in returns the value -.0063. Why?

5

By default, most scientific calculators and formulae assume the angle is being given in radians. Excel is definitely using radians; if you use the pull down menu for functions and select sin, it tells you “Number is the angle in radians for which you want the sine.” Your calculator is evidently set in degree mode. Does it have a deg/grad/rad switch or some such that you can use to change that?

Now that I think about it, the formula you’ve used also assumes radians (whence the 2[symbol]p[/symbol]), so the fact that Excel assumes an angle in radians shouldn’t be a problem, really.

Anyway, just use t = 0, x, 2x, 3x, 4x, etc, where x is something along the lines of 1/20*1/1200, and you should be good to go. You ought to need x to get up to 20 in order to get one complete sinusoid.

6

gr8guy, would you actually mind typing the formula we should use? And are you suggesting several thousand iterations are needed?

Thanks for your help.

7

=SIN(2PI()/100X) for 0 < X <100 will give you a whole cycle.

also

=SIN(2PI()/100(ROW()-1)) if you start in row 1 and go down from there.

8

No problem. Here’s what you’ll want.

In A1, give it the frequency (so you can play with this later, if you want).
In A2, give it =1/201/A1. What this will do is set the size of your time interval so that 20 time steps should give you one full sinusoid.
In the B column, just give it 0, 1, 2, 3, 4, 5, etc.
In the C column, give it C1 = B1 * $A$2, etc.
And in the D column, give it D1 = sin($A$22*PI()*C1), and so on.

C will be your time in seconds and these will be quite small numbers. Basically, this is just because the frequency you’re using is high. If you wanted to plot it out for one full second, you’d see 1200 full sinusoids, which is presumably more than you need, right?

Of course, what sailor wrote will work just as well, and ought to give you a finer grained sine curve to boot, since there’s 5 times as many points to get one full oscillation.

9

Thanks, guys, that did work. Muchos gracias.

Mrs. RickJay’s computering engineering course is the worst-taught course I’ve ever seen, which is why I had to turn to here; the course notes are almost totally irrelevant to what she needs to do. It’s worth a Pit thread on its own. Last night I was attempting to help her with derivatives on her math course again and sat down with her to explain how to find the derivative of a polynomial, where you turn

Y= 3X^2

into

Y`=6X

and such. When I explained how you swing the exponent out front, multiply the polynomial by the exponent, and then subtract 1 from the exponent, she expressed amazement at this remarkable trick. Apparently they haven’t gotten around to teaching that bit yet, you see. And the first test is today, and this is the third week of calculus. Oooookay.

This weekend I get to teach her the chain rule. Won’t that be fun.

So thanks for your help, guys.