Graphs with 8 waves of data (Excel / ppt 2002)

Topic

New Reply

I developed a worksheet that imports a text file each month for what will be 8 waves of data. The attached worksheets shows chart 1 and 2 with the first wave of data in it and an example of what chart 1 looks like.

When the next wave comes i will populate the wave “2” rows which will automatically update the chart with 2 datapoints. As each wave of data is imported the lines of the chart will become longer. My code adds the proper data to the proper wave number row and the chart automatically updates.

My problem is that now they want another chart that shows a summary of the data on one chart. In other words they want the whole row to be a “line” on the chart. See the C3 of my workbook. I know i can transpose the row into a column and use it as the series for the new “line” but i want to be able to easily have the chart update as each wave of data comes in. The i need help with. Does any one have ideas or direction for me to go? Thanks

Reply | Quote

Replies

What do you want to happen as the waves roll in? Your chart in C3 now has 3 lines, based on 1 wave. Should it have 3*8 = 24 lines when all 8 waves are in? Your chart will get pretty crowded! Or should the data from the waves be added? In that case, you might create rows with subtotals and base the chart on those.

Reply | Quote

oh sorry. good question. That is part of the problem, as the new wave comes in it replaces the old wave. So for wave 2, the summary on C3 will only show wave2 data, wave 1 goes away. thanks

Reply | Quote

I don’t know how to do this. It might be a good idea to post the question in the Excel forum with a link to this thread, so that it will come to the attention of the Excel gurus. A moderator will then lock one of the threads.

Reply | Quote

It sounds like you might need to have your summary chart reference a single line of intermediate cells in the worksheet for each data series. That row, in turn, could use an offset formula to the source range, where the number of rows to offset is based on the number of waves of data for which results are held.

For example, change the ‘AA’ series your chart to reference:
TrendGrid!$C$27:$H$27

Then, in TrendGrid!$C$27 put:
=OFFSET(C2,COUNT(C2:C9)-1,)

and copy this across to TrendGrid!$H$27.

Now the AA series on the chart will automatically update when the next wave comes in.

The above approach only works, though, if waves aren’t missed and provided you don’t re-cycle the waves while higher wave numbers are still recorded. If that is a possibility, then you’ll need to have a reference cell, say TrendGrid!$A$27, in which you’d specify the wave to report, and use a lookup formula or combined index/match formula in TrendGrid!$C$27:$H$27 to get the data for the specified wave.

Hope this helps

PS: Note that the second of the above formulae assumes there’ll always be data (0 will do) for each wave in every column. If this is not true, a slightly different formula will be required.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi jha,
How did you get on with this? FWIW, here’s a revised copy of your spreadsheet showing possible approaches along the lines I suggested.
Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

well, i actually copied the rows to a specific place and they overwrite each “wave”. This location is linked to the series and it updates when the new numbers are copied there. thank you very much for your help

Reply | Quote