chart purchases by customer, various items or all together

  • Thread starter Thread starter jvoortman
  • Start date Start date
J

jvoortman

I have a worksheet which shows all the purchases made by a
customer over an extended period of time. Can someone tell
me how to make a "chart" which (hopefully) will show a
line starting on left and heading northbound as it moves
to the right (sort of like what you see when they show the
price of gasoline on the nightly news). I have posted the
dat here http://bbdldarts.homestead.com/excelexample.html,
and I also put commas between columns of info.
It would be nice if I could get a chart that breaks it
down by product and dat, not just total products.
please help if you can

sorry that I posted it in misc also, but I didn't see this
category originally
 
You can turn your data into a pivot table. Select the data, choose the
Pivot Table command from the Data menu. When it presents you with a
blank table (I assume you're using Excel 2000 or later), drag the date
field to the rows area, drag the product code to the columns area, and
drag the case cost to the data area. This gives you a table with a
column of dates next to a column for each product code.

You want cumulative data, so next to the pivot table (skip a blank
column in between), you can put a range of formulas that sum the data
for you. Assuming the pivot table data is in B4:Fwhatever, put this
formula into I4 (or further right if the pivot table takes up more room)
to sum the accumulation in column B:

=sum(B$4:B4)

Fill this across row 4 to do the math for each column, then fill these
down as far as you have pivot table data. As you fill across columns,
the B in the formula changes to C, D, etc. As you fill down, the $4
stays as $4, but the plain 4 increments to 5, 6, 7, etc.

Copy the dates from the pivot table, and paste them in the column just
left of the running sums. This gives you a nice region for the chart.
put labels above all of the columns with sums, and omit the label over
the date column. Select the range and make a line chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top