Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Waste Report charting (2002)

    We are trying to develope a report that tracks waste from our paper coating operation. We want to report this based upon product and week number (example product hqm for weeks 1-37). I have a spreadsheet where our plant manager enters the date, roll length in good material out, and I then calculate the % and plot this against a target. I know how to determin the week number and have a formula for instances where we coat the same product 2 or 3 times for that week number.

    I am trying to figure out how to separate out 2006 data and report this from 2006+2007 combined data.

    Any ideas? The final chart would be a simple column of week number (1-52) vs either lineal feet or % waste for year 2006 and a separate chart for 2007.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Waste Report charting (2002)

    You could add an extra column with formulas that return the year of the dates.
    Create a chart based on all rows.
    Set an AutoFilter on the data. When you select 2006 or 2007 from the filter dropdown for the Year column, the chart will be updated accordingly.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Waste Report charting (2002)

    How are the different products entered?
    Are the products entered on different sheets?
    Are they in different workbooks?
    Are the entries in date order by rows?

    zeddy

  4. #4
    New Lounger
    Join Date
    Jun 2005
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Waste Report charting (2002)

    Hans, I was thinking along a similar line.

    Zeddy,
    Different products have their own tabs (sheets)
    Same workbook
    Entries are by date in rows.

  5. #5
    New Lounger
    Join Date
    Jun 2005
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Waste Report charting (2002)

    Ok, I think I have figured a few things out.

    In my waste report for product "abc" i have the following:
    Column a - date of manufacture
    Column b - input quantity
    Column c - output of good material
    Column d - the difference
    Column e- the % waste for that run

    From this data, I then have a dynamic chart that updates itself as new coating runs are added.

    Way off screen, i have a column with the following formula =if(year(a4)=2006,weeknum(a4,2),"") and in the column next to it a similar formula to give the corresponding waste quantity. In the next 2 columns, I have similar formula spitting out 2007 data. from this I will create another dynamic chart.

    While looking at the 2006 data, I noticed week 40 had 2 coating runs that week.

    I am trying to figure out a formula that will combine these quanities for that week.
    The only thing that I have come up with is: =IF(YEAR($A4)=2006,IF($P4=$P3,D3+D4,D4),"")

    any ideas would be greatly appreciated.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Waste Report charting (2002)

    It would be helpful if you could attach a stripped down copy of your workbook (with fake data if necessary).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •