0

Everyone,

I have an excell sheet which I have imported from my ERP program. It contains data about deviations in raw materials which were noted at specific dates. There are 40 different materials and data was gathered throughout the last year. The raw data looks like this:

Material name | Date | Deviation Blue dye |2014.05.01| 50 Yellow dye |2014.07.02|-40 Blue dye |2014.07.04| 10

How can I transform this data to a stock-type chart which would should cumulitive deviations throughout the year (i.e. if Blue dye is always positive, how much had added up on each date). I have figured out how to sum up the deviations with their previous values, I have also transformed the table so that all the materials have their deviations in a seperate row:

Material name1|Date1|Date2|Date3 |50 |-10 |20 Material name2|Date2|Date5|Date6 |5 |10 |-100

The problem is that the deviations don't happen on the same dates. If they were noted on the same day every week, this would be hard at all. In this case each material might not have a deviation for a month or two, while another has fluctuations every couple of days. I would need to somehow interpolate the data in between the dates, so that every day of the year is filled up. I would appreciate any ideas, at this point I'm just stuck...

Karolis Hematogenas
  • 5
  • 3

2 Answers2

0

I would suggest using the original data but creating a table on a new tab, list the entire year in column A from A2 down then list the 40 materials across in B1,C1,D1...etc

Then Starting in first cell (B2) use SUMIF(AND functions to match the date in A2 and the material in B1. if there is a match then +/- the difference...perhaps start one cell lower down and use B1,C1 etc for the starting number to perform calculations...this should give you the holding at any one point.

Hope to have helped :)

DCX
  • 102
  • 5
0

I thought the above may have been a little vague...i've done you a quick example at the link below - there are 3 tabs

1 for the raw data 2 to get the differences by date & material 3 to show stock holding each day by material (with no change should there be no change)

Assuming you wanted to graph this info by date/product you should have no problem doing this from the example.

hope this is of more help!

http://www.filedropper.com/materialexample

DCX
  • 102
  • 5