0

I don't even really know how to search for this, so I apologize if this has been asked already. I couldn't find anything.

I have data for a bunch of tests which is structured like so:

+---------+---------+-----------+-----------+ | TESTNUM | TEST_HR | CHEM1_HRX | CHEM2_HRX | +---------+---------+-----------+-----------+ | 1 | 1 | 5 | 3 | | 1 | 2 | 6 | 7 | | 1 | 3 | 10 | 15 | | 1 | 4 | 15 | 25 | | 2 | 1 | 4 | 3 | | 2 | 2 | 5 | 9 | | 2 | 3 | 9 | 18 | | 2 | 4 | 12 | 30 | +---------+---------+-----------+-----------+

This chart is being pumped in by a database query, so I want it to be expandable and dynamic. It's basically two tests worth of chemical analysis, where a sample is taken every hour and tested. I cannot seem to wrap my head around a method of using a pivot table to display a scatter plot of TEST_HR and CHEM_1_HRX, and similar for CHEM_2_HRX. I had a method going using just tables, and it worked well, but required rework every time I updated the query to include more tests.

HudsonMC
  • 170
  • 2
  • 9

1 Answers1

1

It seems that Pivot Tables don't support Graphing to XY (Scatter), bubble, or Stock chart types. But if you use line graph, and format the graph to remove the lines, it will look some what like a XY scatter graph.

Can you provide an example of what the chart should look like with the sample data you provided? (I would comment, but don't have enough rep points yet).

Namkce
  • 504
  • 5
  • 16
  • Sure. It's not so much the charting I'm worried about, but the filtering of the data. I'd basically like a chart that would have rows for each test number (1 and 2), columns for each test hour (1, 2, 3, and 4), and the chemical data listed in the data portion of the chart. I don't know if I'd need one chart for each chemical test, or what. Does that make sense? – HudsonMC Mar 04 '16 at 21:50
  • I wrote a post on filtering pivots based on external ranges at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ that may be useful. – jeffreyweir Mar 05 '16 at 01:02