Re: [libreoffice-users] Linear Graph with X,Y data

At 11:40 17/02/2016 -0800, Joel Madero wrote:
> 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).

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

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted