charting min/max

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I am importing data from a couple of guages. The data comes in with a date,
time, and value. This happens every 15 minutes 24x7

Currently what I am doing after the import, is using subtotal on every date
change to get the days min, max, and average. That creates the values in
rows unfortunately. I'd like to create a line chart with one line being the
daily min, one line being the daily max and one being the daily average.
I'm having trouble doing this without a lot of transposing data.

A sample of my source data range (with the subtotals collapsed):

3/3/2004 Var 1.48
3/3/2004 Min 70.5
3/3/2004 Max 81
3/3/2004 Average 71.6
3/4/2004 Var 0.34
3/4/2004 Min 68.4
3/4/2004 Max 71.9
3/4/2004 Average 69.6
3/5/2004 Var 0.24
3/5/2004 Min 68
3/5/2004 Max 70.2
3/5/2004 Average 68.9


I've also tried using pivots on the raw data. That gives me the same
results and with similar charting problems.

Grateful for any ideas!

Kevin

--
 
This looks like a good candidate for a pivottable and then a pivot chart.

When you did your pivottable, did you drag the quantity field into the data
field 4 times--once each for min/max/average and variance?

Then drag that data button one cell to its right (right on top of the cell
marked "Total") to make a table like:


Data
date Average of qty Max of qty Min of qty Var of qty
03/08/2004 23 28 19 21
03/09/2004 20 27 9 49.42857143
03/10/2004 15 20 7 49
03/11/2004 7.5 12 3 40.5
03/12/2004 26 29 23 18
03/13/2004 19 30 4 181
03/14/2004 14.33333333 25 1 149.3333333
Grand Total 18.375 30 1 75.11413043


After I did this, I clicked on the chart wizard icon. I got sometype of bar
chart.

I rightclicked on the chart and selected chart type and changed it to some kind
of line chart.
 
Thanks Dave - I think it was the "drag that data button one cell to its
right" that I was missing. On my small sample set that looks like it will
work. Will know definitively tomorrow.

Thanks
 
Back
Top