Calculate work-in-process historical data

[freezer]

Hi everyone!

My question is as follows:

I have a database that shows the start and exit dates (for each entry a separate start and exit date) from a commodity that is going through processing. The commodity has 3 different categories and each entry (lot) has a different volume. The database shows everything neatly into columns.

How can I calculate the daily volume inside of the production process of last year (the work-in-process)? And how do I integrate the separation of categories? The objective is to create a solid basis to do projections for the growing capacity for upcoming year.

It is a seasonal product, and there is a lot of difference between the days in processing (also due to the process) between each lot and I would therefore like to avoid averages.

Greets,

Freezer

 

[J.Ty.]

Hi,

Could you, please, post a small sample of your data and the output you would expect from this sample?

J.Ty.

 

[freezer]

Hi,

Could you, please, post a small sample of your data and the output you would expect from this sample?

J.Ty.

Off course!

This is the data I use, next to each other:

[TABLE="width: 342"]
<tbody>[TR]
[TD]Certification[/TD]
[TD]Entry[/TD]
[TD]Exit[/TD]
[TD]Ticketnum[/TD]
[TD] Volume[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]28-1-2014[/TD]
[TD="align: right"]1251[/TD]
[TD="align: right"]14,09[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]23-1-2014[/TD]
[TD="align: right"]1252[/TD]
[TD="align: right"]1,34[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]20-1-2014[/TD]
[TD="align: right"]1253[/TD]
[TD="align: right"]3,99[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD] 10-1-2014[/TD]
[TD="align: right"]7510[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]17-1-2014[/TD]
[TD="align: right"]7511[/TD]
[TD="align: right"]1,08[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]21-2-2014[/TD]
[TD="align: right"]7512[/TD]
[TD="align: right"]1,2[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]10-2-2014[/TD]
[TD="align: right"]7513[/TD]
[TD="align: right"]44,5[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]23-1-2014[/TD]
[TD="align: right"]7514[/TD]
[TD="align: right"]4,21[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]13-2-2014[/TD]
[TD="align: right"]7515[/TD]
[TD="align: right"]4,25[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD] 20-1-2014[/TD]
[TD="align: right"]7516[/TD]
[TD="align: right"]0,7[/TD]
[/TR]
[TR]
[TD]Convencional[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]17-1-2014[/TD]
[TD="align: right"]1249[/TD]
[TD="align: right"]2,54[/TD]
[/TR]
[TR]
[TD]Convencional[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]13-2-2014[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]1,46[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]2-1-2014[/TD]
[TD="align: right"]20-1-2014[/TD]
[TD="align: right"]1254[/TD]
[TD="align: right"]1,4[/TD]
[/TR]
[TR]
[TD]RFA[/TD]
[TD="align: right"]2-1-2014[/TD]
[TD="align: right"]16-1-2014[/TD]
[TD="align: right"]1255[/TD]
[TD="align: right"]12,33[/TD]
[/TR]
[TR]
[TD]FTO[/TD]
[TD="align: right"]2-1-2014[/TD]
[TD="align: right"]4-2-2014[/TD]
[TD="align: right"]5466[/TD]
[TD="align: right"]32,29[/TD]
[/TR]
[TR]
[TD]FTO[/TD]
[TD="align: right"]2-1-2014[/TD]
[TD="align: right"]14-1-2014[/TD]
[TD="align: right"]5467[/TD]
[TD="align: right"]15,1[/TD]
[/TR]
</tbody>[/TABLE]

Output:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Certification[/TD]
[TD]Volume (in process)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 jan[/TD]
[TD]FLO[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RFA[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Conventional[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 jan[/TD]
[TD]FLO[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RFA[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Conventional[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 jan[/TD]
[TD]FLO[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope this helps!

Freezer

 

[J.Ty.]

I have made a few corrections to your data:
1) corrected typos (FTO/FLO and Conventional/Convencional)
2) Added year number to the dates in the report part (otherwise you would get strange results around year turn)
3) Put the date in every row of the report part (simplifies the formulas)

And then I propose the following (fill down the formula):

Excel 2010
	A	B	C	D	E
1	Certification	Entry	Exit	Ticketnum	Volume
2	RFA	2014-01-01	2014-01-28	1251	14,09
3	RFA	2014-01-01	2014-01-23	1252	1,34
4	RFA	2014-01-01	2014-01-20	1253	3,99
5	RFA	2014-01-01	2014-01-10	7510	15
6	RFA	2014-01-01	2014-01-17	7511	1,08
7	RFA	2014-01-01	2014-02-21	7512	1,2
8	RFA	2014-01-01	2014-02-10	7513	44,5
9	RFA	2014-01-01	2014-01-23	7514	4,21
10	RFA	2014-01-01	2014-02-13	7515	4,25
11	RFA	2014-01-01	2014-01-20	7516	0,7
12	Conventional	2014-01-01	2014-01-17	1249	2,54
13	Conventional	2014-01-01	2014-02-13	1250	1,46
14	RFA	2014-01-02	2014-01-20	1254	1,4
15	RFA	2014-01-02	2014-01-16	1255	12,33
16	FTO	2014-01-02	2014-02-04	5466	32,29
17	FTO	2014-01-02	2014-01-14	5467	15,1
18
19
20	Date	Certification	Volume (in process)
21	2014-01-01	FTO	0,00
22	2014-01-01	RFA	90,36
23	2014-01-01	Conventional	4,00
24	2014-01-02	FTO	47,39
25	2014-01-02	RFA	104,09
26	2014-01-02	Conventional	4,00
27	2014-01-03	FTO	47,39
Sheet2

Cell Formulas
Range	Formula
C21	=SUMIFS($E$2:$E$17,$B$2:$B$17,"<="&$A21,$C$2:$C$17,">="&$A21,$A$2:$A$17,"="&$B21)

Best,

J.Ty.

 

[freezer]

Thanks! I'm gonna give it a try now!

Freeze

ps. the typo had to do with the spanish to english which I only partly did.

 

[freezer]

works perfect! Thanks J.Ty

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.