Is it possible to chart series data from multiple rows in a table?

G

Guest

I am trying to chart a series of data from multiple rows - ref the following
table:

YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598
1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538
1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139

Can this be done without reformating the table? If so how?

Thanks in advance for your help!
 
J

Jon Peltier

Rule of thumb:

If you spend five minutes fixing up your data, you will save five hours of
frustration trying to make the unfixed data work. I think in the long run it
will be easier if your data is converted to two columns, one with date, the
other with values.

Even to do this without rearranging the main block of data requires adding
dummy rows to get the data to flow from one year to the next. For example, I
inserted three rows, and in cell B2 (assuming it all starts in B1) I entered
this formula:

=DATE($A5,MATCH(B$1,$B$1:$M$1,0),1)

and I filled this right and down to fill in all the dates.

YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
1/1/1992 2/1/1992 3/1/1992 4/1/1992 5/1/1992 6/1/1992 7/1/1992 8/1/1992
9/1/1992 10/1/1992 11/1/1992 12/1/1992
1/1/1993 2/1/1993 3/1/1993 4/1/1993 5/1/1993 6/1/1993 7/1/1993 8/1/1993
9/1/1993 10/1/1993 11/1/1993 12/1/1993
1/1/1994 2/1/1994 3/1/1994 4/1/1994 5/1/1994 6/1/1994 7/1/1994 8/1/1994
9/1/1994 10/1/1994 11/1/1994 12/1/1994
1992 164278 164864 164221 165084 165920 166544 167284 168184 170158
170859 171531 173598
1993 175683 174121 172829 176710 178999 178772 180987 180996 182227
183233 185266 186538
1994 185948 188562 191756 192275 191300 193606 194229 196568 197876
200281 200301 201139


Then I selected row 2, and held CTRL while selecting row 5, and I made an XY
chart. Then I selected row 3 and CTRL selected row 6, copied this, selected
the chart, and used Edit menu > Paste Special to add the data as a new
series*. I repeated this with row 4 and row 7.

*You can also add the data as new points, which puts them into the original
series.

- Jon
 
D

David Biddulph

Yes, but what sort of chart are you trying to produce?

Do you want just one series covering the 3 years, or one series for each
year? If the former, what are you using as your X-values (category labels)?
 
G

Guest

Thanks for the quick reply. Not what I had hoped to hear but confirms what I
found. It can be done but time consuming and cumbersome. So I will have two
tables - one to present / maintain data in table format and another to chart.
Thanks again.
 
G

Guest

I am combining data - line and bar - using two series of data on two axes.

I want one series for 3 years for each axes. X value will be the year
segmented by months.
 
J

Jon Peltier

This actually is a step towards my own best practices. I have multiple data
tables, all linked to the original or raw data. The others will be optimized
for charting, for on-screen display, for printed output, for exporting to
Word, and for whatever other uses I may have. Worksheets are cheap, and the
time spent setting up a linked and formatted table is much much less than
the time spent trying to make one table serve multiple purposes. I repeat my
first line:

If you spend five minutes fixing up your data, you will save five hours of
frustration trying to make the unfixed data work.

- Jon
 
M

mrobles

I'd like to revisit this topic. We have very detailed spreadsheets of data
and the row labels change position from year to year, but it's not an option
for us to standardize them because it would make adding new data more
difficult.

That said, how would I edit the series values of a graph so that they could
pull from one row until a certain column is reached, then switch to a
different row. Here's an example.


2000 2001 2003 2004
Ford 14 16 Toyota 30 31
Toyota 32 33 Jeep 25 22
Jeep 26 20 Ford 14 18

How would I graph Ford from 2000 to 2004?

Thank you.
 
D

Del Cotter

I'd like to revisit this topic. We have very detailed spreadsheets of data
and the row labels change position from year to year, but it's not an option
for us to standardize them because it would make adding new data more
difficult.

That said, how would I edit the series values of a graph so that they could
pull from one row until a certain column is reached, then switch to a
different row. Here's an example.
Click to expand...

Give the two tables Range Names, say TABLE1 and TABLE2. Then use VLOOKUP
to find Ford in the table that corresponds to the appropriate year:


table TABLE1 TABLE1 TABLE2 TABLE2
column 1 2 1 2

2000 2001 2003 2004
Ford X X X X
Toyota X X X X
Jeep X X X X

Everywhere I have put "X", put a formula like:

=VLOOKUP($A1,table,column,FALSE)

to find your data in the tables. Then use the final table as a source of
your chart.
 
Top