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:

Certification	Entry	Exit	Ticketnum	Volume
RFA	1-1-2014	28-1-2014	1251	14,09
RFA	1-1-2014	23-1-2014	1252	1,34
RFA	1-1-2014	20-1-2014	1253	3,99
RFA	1-1-2014	10-1-2014	7510	15
RFA	1-1-2014	17-1-2014	7511	1,08
RFA	1-1-2014	21-2-2014	7512	1,2
RFA	1-1-2014	10-2-2014	7513	44,5
RFA	1-1-2014	23-1-2014	7514	4,21
RFA	1-1-2014	13-2-2014	7515	4,25
RFA	1-1-2014	20-1-2014	7516	0,7
Convencional	1-1-2014	17-1-2014	1249	2,54
Convencional	1-1-2014	13-2-2014	1250	1,46
RFA	2-1-2014	20-1-2014	1254	1,4
RFA	2-1-2014	16-1-2014	1255	12,33
FTO	2-1-2014	4-2-2014	5466	32,29
FTO	2-1-2014	14-1-2014	5467	15,1

<tbody>
</tbody>

Output:

Date	Certification	Volume (in process)
1 jan	FLO	?
	RFA	?
	Conventional	?
2 jan	FLO	?
	RFA	?
	Conventional	?
3 jan	FLO	?

<tbody>
</tbody>

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.