User profile for user: theleonhearted
theleonhearted Author
User level: Level 1
0 points

Help Creating a Sine Wave in Numbers '09?

Hi,
I'm having trouble graphing some data in Numbers, having been used to Excel for most of my life. What I've basically got to do is graph a sine wave representing the annual variation described in the graph (it compares CO2 concentration measured at a chain of islands to time in years). I've figured out easily how to graph the data on a polynomial treadline curve, but now I've got this to do.
The instructions say to make a new column in column D and enter in the equation 1.435x-2501.1, which is derived from the linear fit treadline equation I had calculated earlier. After that I am to copy it down to match the number of cells in column A (626), which I did, so now every cell in D down to 626 contains that equation above. Then, I enter 1 in cell E1 and 1/12 in order to arbitrarily guess at the sine wave amplitude and period, respectively, and finally I then select cell D1 and put in this equation:
=1.435*A2-2501.1+($E$1*SIN(A2/$F$1))
After that I grab cell D2, drag it down to select all the cells in column D, and add it to the graph by (I think) dragging it onto the graph, as Numbers says to do. What I'm uncertain of is where to go from here. Here are the instructions:
'To plot the model values on the chart, double click or right click on the chart and select "Select Data" and click on Add under Series. Name the series "Model" and select data in column A for the X values and data in column D for the Y values. Rename Series 1 "Raw Data" and click OK."

That's all stuff you can do in Excel... I don't know how to do it in Numbers. When I drag D into the graph, it works fine, but when I go to add in column A for X and D for Y in Inspector it won't let me type anything. How do I complete this sine wave?

Thank you very much in advance for reading through this and helping me.

macbook Pro, Mac OS X (10.6.4)

Posted on Sep 17, 2010 4:52 PM

Reply
14 replies
User profile for user: Barry
Barry
User level: Level 9
60,048 points

Sep 17, 2010 7:16 PM in response to theleonhearted

theleonhearted wrote:
The instructions say to make a new column in column D and enter in the equation 1.435x-2501.1, which is derived from the linear fit treadline equation I had calculated earlier. After that I am to copy it down to match the number of cells in column A (626), which I did, so now every cell in D down to 626 contains that equation above.


I'm not sure of the reason for this step.
1.435x-2501.1
Entered into D2, with the x value in A2 is expressed as:
=1.435*A-2501.1
which is part of the formula below.

Then, I enter 1 in cell E1 and 1/12 in order to arbitrarily guess at the sine wave amplitude and period, respectively, and finally I then select cell D1 and put in this equation:
=1.435*A2-2501.1+($E$1*SIN(A2/$F$1))


I'm assuming "1/12" goes in F1.
I've also assumed row 1 is a Header row, and have entered your formula into the first data row (D2)

Your formula is now:
(1.435 times the value A) - 2501.1 + 1 * (Sin( the value in A divided by 0.833...))
or
(1.435 times the value A) - 2501.1 + 1 * (Sin( the value in A multiplied by 12))

You haven't given any specifics regarding the raw data, so I've arbitrarily set it to a positive linear slope by making A =ROW()-1.

After that I grab cell D2, drag it down to select all the cells in column D, and add it to the graph by (I think) dragging it onto the graph, as Numbers says to do. What I'm uncertain of is where to go from here. Here are the instructions:
'To plot the model values on the chart, double click or right click on the chart and select "Select Data" and click on Add under Series. Name the series "Model" and select data in column A for the X values and data in column D for the Y values. Rename Series 1 "Raw Data" and click OK."


These may be instructions for Excel. Using Numbers, a simpler way to place thie data into an X-y Scatter plot is this:

Set the data up to occupy the full columns A and D, not including the Header row mentioned above.
Select Column A: Click on any cell to make the table active show the row and column Reference tabs.
Click on column A's reference tab.
Command-clickon column D's reference tab to add it to the selection.
Click the Chart button in the tools and choose the Scatter chart icon from the menu.

If I've read this correctly, your formula imposes a sine wave onto the set of data.
Here are the results. The expanded chart at the right shows the approximately the area bounded by the light blue rectangle at the upper end of the plot in the larger chart.

User uploaded file

Regards,
Barry
Reply
User profile for user: theleonhearted
theleonhearted Author
User level: Level 1
0 points

Sep 17, 2010 7:32 PM in response to Barry

Thank you so much for replying to me.
Yes, 1/12 goes into F1. I'm sorry, I must have omitted that by mistake. Row 1 is actually the row in which I entered the equation; it has no header.
Do you mean to say I should change the formulas to match those in your reply? I'm sorry if that sounds ignorant, but I'm so confused on this thing...
If it helps, the final graph raw data line is meant to look like the model line, which is the line I got when plugging in the polynomial trendline - it's that jagged curve. What it's meant to end up looking like is that curved jagged line going up, and a second smaller jagged straight, linear trendline running through it. I hope that describes it sufficiently.
I'm going to try and do as you said now, see what happens... thank you again.
Reply
User profile for user: Barry
Barry
User level: Level 9
60,048 points

Sep 17, 2010 9:00 PM in response to theleonhearted

theleonhearted wrote:
Thank you so much for replying to me.
Yes, 1/12 goes into F1. I'm sorry, I must have omitted that by mistake. Row 1 is actually the row in which I entered the equation; it has no header.


A "Header row" is functionally different from a 'regular' row. The blank chart template produces a table with one header row (row 1) and one header column (column A). Neither of these should contain data.
Setting a row as a 'header row' applies to the whole row, not just selected cells in that row.

You can check whether Column A has been set as a Header column by looking at the popup menu in the column's reference tab.

Click any cell to make the table active and display the column and row reference tabs. Place the mouse pointer over the right end of column A's reference tab, and click on the triangle that appears.
• If the popup menu includes "Convert to Header Row", then Column A is currently a 'regular' column. Leave it as such, and feel free to include data in the column.
• If "Convert to Header Row is NOT included in the popup menu of column A, then column A is already a header column, and you should not include data in that column. Let us know if that is the case, and we can offer suggestions on moving the data.

Do you mean to say I should change the formulas to match those in your reply? I'm sorry if that sounds ignorant, but I'm so confused on this thing...


There's no need to be sorry for ignorance—the word simply means lacking knowledge, and applies to all of us in one or more areas. I also think we share some of that confusion.

A question: In Excel, would you be making use of Excel's "Solver" (or "Problem Solver") feature in any of this? Numbers does NOT include such a feature, and that difference may be what's leading to confusion.

The answer may affect whether changing the formulas to what I've suggested is or isn't a useful thing to do.

If it helps, the final graph raw data line is meant to look like the model line, which is the line I got when plugging in the polynomial trendline - it's that jagged curve. What it's meant to end up looking like is that curved jagged line going up, and a second smaller jagged straight, linear trendline running through it. I hope that describes it sufficiently.


Unfortunately, it doesn't. I don't know your data, so I don't know what 'the model line' or the trend lines should look like.

I'm going to try and do as you said now, see what happens... thank you again.


OK. I'd suggest working with a COPY of your file while experimenting.

Regards,
Barry
Reply
User profile for user: theleonhearted
theleonhearted Author
User level: Level 1
0 points

Sep 17, 2010 9:27 PM in response to Barry

I truly appreciate your understanding.
Yes, Excel's problem solver function was quite a staple for me in the past. The fact that Numbers lacks this certainly doesn't help the situation.
I checked the rows and they're set properly.
I'm thinking maybe Numbers doesn't have a sinewave setting such that the instructions are asking for... I'm going to ask for help tomorrow from my professor, who gave me the assignment.
Thank you again so much.
Reply
User profile for user: Badunit
Badunit
User level: Level 7
31,122 points

Sep 17, 2010 10:15 PM in response to theleonhearted

It sounds like you have figured out how to make the table of data and your question is about how to create the chart. I am assuming you have already plotted a scatter chart of columns a (your X data) and column B (Y data) and now need to add a series for column D.

Select the chart
Go to your table and click the gear in the upper left corner of the colored selection box.
Choose to share x values.
Now drag D into the chart

If you drag D into the chart first instead of as the last step, choosing to share X values may activate it or may cause weird behavior.

Message was edited by: Badunit
Reply
User profile for user: KOENIG Yvan
KOENIG Yvan
User level: Level 8
41,825 points

Sep 18, 2010 1:35 AM in response to theleonhearted

User uploaded file

Here I changed one parameter because with the original one, the sine was so close of the line that itv wasn't legible.

In cells A2 and C2, the formula is :
=ROW()-1
In cell B2, the formula is :
=1.435*A2-2501.1
In cell D2 the formula is :
=B2+$E$1*IFN(A2/$F$1)

In E1, I replace 1 by 20
In F1, the formula is =1/12

I applied fill down to columns A thru D

I selected A1…D627
asked for a scatter chart.

I applied these settings :
points : none
rely points by curve
set curve stroke to 0.25 point.

I guess that you didn't filled the column C with the X values required to scatter chart two series.

Yvan KOENIG (VALLAURIS, France) samedi 18 septembre 2010 10:35:22
Reply
User profile for user: Barry
Barry
User level: Level 9
60,048 points

Sep 19, 2010 2:21 AM in response to theleonhearted

theleonhearted wrote:
What I'm meant to do is make the table A my X data and the table D my Y data. This is what I don't know how to do.


You can do this using at least two methods:

1. Hide columns B and C using the Hide column menu item in the reference tab for each column.
Select the now adjacent columns A and D as you would select any two adjacent columns.

OR

2. Click on column A's reference tab to select column A. Command Click on column D's reference tab to add column D to the selection.

After selecting the two columns, make the chart.

Regards,
Barry
Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Help Creating a Sine Wave in Numbers '09?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up