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...
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!