Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have one monthly data series in one spreadsheet and one quarterly in a different spreadsheet. What is the "smartest" way to plot them together in one graph (in Excel)? Its quite a lot of data, so I would like to avoid fiddling with it manually.

EDIT: The data is laid out in the following way:

Monthly:

2011-12-01  1246.91
2011-11-01  1251
2011-10-03  1131.21
2011-09-01  1219.12
2011-08-01  1292.59
2011-07-01  1320.64
2011-06-01  1345.2

Quarterly:

1947q1  237.2
1947q2  240.4
1947q3  244.5
1947q4  254.3
1948q1  260.3
1948q2  267.3
1948q3  273.8
1948q4  275.1

We can assume that 1947q1 is the same day as the corresponding monthly data for January.

share|improve this question
    
Can you give us an idea how the data is layed out? –  CharlieRB Dec 30 '11 at 13:58
    
@CharlieRB, thanks for the suggestion - I added it to the question. –  Grzenio Dec 30 '11 at 14:13
    
The Community bot randomly stirs up old questions without accepted answers and recirculates them. This one popped up from 3 yrs ago. This is a unique, user-specific requirement. There is no obvious relationship between the monthly and quarterly data, which is half a century apart. "What is the smartest way to plot them together in one graph" depends entirely on what you are trying to illustrate, which only you know. Without something like a mockup or very clear description, nobody can answer this. Do you still need an answer? If so, can you provide clarity? –  fixer1234 Dec 8 at 6:00

1 Answer 1

First you have to decide what do you want to plot. For instance, do you want a monthly or quarterly chart?

If you want a monthly chart, you need to decide wheter the quarterly data will be paired to the monthly data by repeating the quarter's value each month, by charting just the end of period, or something else.

If you want a quarterly chart, you need to decide wheter the monthly data will be paired to the quarterly data using the quarterly average, the end of period value, or some other transformation.

This decisions will depend on what you need to chart - there is no simple answer.

As on how to pair the data, use a pair a formulas to create an identifier on which you can perform lookups. Like:

=year()*10+month()  and pass a date as parameter
=value(replace("2003q2";"q";""))

both will yield months in the 20031 format, making it easy to use lookup functions.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.