Linear Graph with X,Y data

users
#1

Hi All,

Please see this file:
https://drive.google.com/file/d/0B2kdRhc960qdV2dTVUh5aWZIUWc/view?usp=sharing

I'm hoping to create a linear graph from January of 2007 -> Current
Month using this data. I can't think of a way to do this without putting
the data in a different format (possible but not ideal). Can anyone
think of a different solution for a single line chart over time (I don't
want a different line per year or month).

Thanks in advance.

Best,
Joel

#2

In row 1 of a spare column, enter:
=DATEVALUE(OFFSET(A$1;MOD(ROW()-1;12)+1;0)&OFFSET(A$1;0;INT((ROW()-1)/12)+1))
and fill down the column as necessary. The first OFFSET(), using MOD(), harvests the month names and the second OFFSET(), using INT(), harvests the year numbers; DATEVALUE() then assembles the monthly date values from the concatenation of the two. (It appears that no intervening space is necessary.)

In row 1 of the following column, enter:
=OFFSET(A$1;MOD(ROW()-1;12)+1;INT((ROW()-1)/12)+1)
and fill down similarly. This formula harvests the currency amounts.

You now have two columns from which you can readily create your chart. The two columns used intermediately can be hidden or placed on another sheet (or both) if preferred. Since the new columns are derived from your original table, not simply a copy of its values, you can continue to modify data in the table and see it reflected in the chart.

I'm attaching a copy including a sample chart, though I imagine only you will see this.

I trust this helps.

Brian Barker