Help to plot data by chemical by week

**dtgheath** · 04-26-2011, 02:22 PM

Hi,

Attached is a example of the file that I have. I have data that stretches back 4 yrs. I would like to look at the annual data by week number. I would like to chart by chemical the weekly amount of chemical used.

Eg WW1 Chem A used 4
WW2 Chem A used 4

etc.

Is there a quick way to plot this data by week automatically?

Thanks in advance for any help

**ChemistB** · 04-26-2011, 02:24 PM

Pivot Tables are the quickest way to handle this. What version of Excel are you using?

**ChemistB** · 04-26-2011, 02:28 PM

Based on the xlsx file. Go to the Insert Tab of the ribbon and click on Pivot Table. Select your complete range i.e. Sheet1!$A$1:$G$31 and insert on a new sheet
Then move WW to Row labels, followed by Description to Row Labels, then Qty to Values.
Does that work for you?

**dtgheath** · 04-26-2011, 02:28 PM

In work I use 2003, and have a pivot table but thought there might be a easier way.

With the pivot table can you have multiple of charts to show each chemical?

**ChemistB** · 04-26-2011, 02:30 PM

You want bar charts with week on x and qty on y?

**dtgheath** · 04-26-2011, 02:34 PM

You want bar charts with week on x and qty on y? Yes, but for each chemical....

I have the pivot hart which shows all chemicals, I know I can select or de-select the chemicals and update the data to alter the chart but can I have a chart for each chemical or do I need to do what I have been doing (de-select the chemical?)

**ChemistB** · 04-26-2011, 03:15 PM

Another alternative with the pivot chart is to put the description in the Report Filter field.
which still gives you 1 chart at a time but easier to navigate (See attachment)

OR, you can use SUMPRODUCT to make a table and then grab one row at a time and "Insert Column Chart"
In my example, in K3, dragged across and down is
=SUMPRODUCT(--($D$2:$D$26=$J3),--($A$2:$A$26=K$2),$E$2:$E$26)
I made the first 2 charts for you.