How do I derive a graph using dates, and then plot that graph using the original dates?

[radiotower]

Right now, I have one graph in excel (see link).


I want to add another graph in the same chart. This second graph can be obtained by multiplying each x value (the dates) by 2. Then, I want to plot both graphs in one chart.

Other forums where I posted this: http://www.mrexcel.com/forum/newthre...newthread&f=10

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[radiotower]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

I made the necessary changes.

[MrShorty]

What does "multiplying each x value (the dates) by 2" mean to you?

The basic approach would be to:
1) Enter a column between A and B
2) Enter formula in B that represents the "multiply dates by 2" operation (=A2*2 or whatever).
3) Select B and C, copy, select chart, paste special, as new series with X values in left column but don't replace existing categories.
4) Format chart as needed.

I find that scatter charts are often easier to work with than line charts, but you lose the nice "date" axis option. I might change the chart type, but that will depend a lot on exactly what you want the end result to look like.

[newdoverman]

Is this by chance similar to what you are wanting? If yes, I couldn't do this on the chart worksheet but I could do it on the Information worksheet. I just created a new column as suggested by MrShorty, copied, clicked on the existing chart that I previous made that looks like the one you created and used Paste Special and this was the result. The sine waves are offset by the date differences that I created with =edate(a2,4) which gives an offset of 4 months.
Graph Capture.JPG

[radiotower]

Is this by chance similar to what you are wanting? If yes, I couldn't do this on the chart worksheet but I could do it on the Information worksheet. I just created a new column as suggested by MrShorty, copied, clicked on the existing chart that I previous made that looks like the one you created and used Paste Special and this was the result. The sine waves are offset by the date differences that I created with =edate(a2,4) which gives an offset of 4 months.
Attachment 496205

I am looking to show two different periods in one graph. I found a random website that explains what I am looking for.

http://www.dummies.com/education/mat...sine-function/

The two graphs in that link have the same function (except for the constant), the same date range, but one repeats more often.

[MrShorty]

So it is not a phase shift like newdoverman's example.

Your sample spreadsheet does not include the period, nor are you using a sin/cos function to compute column B. Do you need to derive/regress the period etc. from the existing data, or do you have the period (and amplitude etc.) somewhere else? Using your trig lesson, I would expect that column B is computed from some variation of =SIN(period*date). Something that computes at twice the period would look like =SIN(2*period*date).

If you need to derive/regress the period from the existing data, then we can talk about non-linear regressions in Excel using Solver or research Fourier regression in Excel.

[radiotower]

So it is not a phase shift like newdoverman's example.

Your sample spreadsheet does not include the period, nor are you using a sin/cos function to compute column B. Do you need to derive/regress the period etc. from the existing data, or do you have the period (and amplitude etc.) somewhere else? Using your trig lesson, I would expect that column B is computed from some variation of =SIN(period*date). Something that computes at twice the period would look like =SIN(2*period*date).

If you need to derive/regress the period from the existing data, then we can talk about non-linear regressions in Excel using Solver or research Fourier regression in Excel.

This is what I used to get the graph: http://www.tradingfives.com/fourier_cycle.html.

The magenta graph is what I have now. I am trying to use this graph to plot other graphs with shorter/longer periods.

[MrShorty]

Accidental Duplicate without attachment

[MrShorty]

The overall period of a trig function is determined by the multiplier for t. In tradingfives example, the trig functions have periods are controlled by the 2*pi()/365 (part of e) and 4*pi()/365 (2e) quantities in the function. To get something with a different period, change the multipliers for e. If you want something with one-half the period, use 2e and 4e instead of e and 2e. If you want something with twice the period, use e/2 and e instead of e and 2e.

Again, starting from tradingfives spreadsheet, I would be inclined to add another e column that =$B2*multiplier, then add additional columns for each trig function based on the new e column. Finally, compute the overall y and plot that column.

[newdoverman]

To get the double dates which I assume to be two times in one day I added .5 days to each date in Information column A into column C. Then in column D dates in column A and Column C are combined in column D with this formula entered into D3 and filled down.

=IF(ROW()>COUNT(A:A)+COUNT(C:C),"",IF(MOD(ROW(),2)=1,INDIRECT("A"&ROW()-INT(ROW()/2)),INDIRECT("C"&ROW()-INT(ROW()/2))))

The regression data was then copied and pasted into column E a couple of times to fill the data series requirements. I know that the regression isn't correct but I will leave the mathematics of the regression to you.

A line chart was then created on the Information worksheet. The curve was selected and copied.
On the Chart worksheet, the chart area was selected and then Paste Special was used to paste the values into the original chart. Note the I labelled the regression data as Regression1 and Regression2 to keep the data identifiable. After pasting, click on the format tab, choose in the Current Selection Group "Regression2" then click on Format Selection and choose Secondary Axis.
You should now have a chart with 2 regression lines one with twice the frequency of the other and you can now delete the chart created on the Information worksheet.

[newdoverman]

Here is a slightly improved version of my previous post.
Formula to combine columns: Enter in D2 and fill down

=IF(ROW()>(COUNT(A:A)+COUNT(C:C)+2),"",IF(MOD(ROW(),2)=0,INDIRECT("A"&ROW()-INT(ROW()/2)+1),INDIRECT("C"&ROW()-INT(ROW()/2))))