Need Formula that Calculates Sums of Multiple Cycles Within Column

[AJRIGNEY]

Hey everyone. I'm currently a student and my studies have nothing to do with excel, but I have am in the middle of an internship where all I do is work with spreadsheets. Currently, I'm working with a paper company in Alabama, where they asked me to create a spreadsheet that measures efficiency. While I've made great progress on the project, I just came up on a little snag and was wondering if you might be able to help.

I was asked to calculate what is known as "cooktime" per every cycle (each cycle culminates in what is known as "blowtime". "Cooktime" refers to the total number of minutes measured from when pressure reaches a certain point until the blowtime.

A simple version of what I'm trying to do looks like this (Same example in .xls attachment):


A B C D
TIME HFACTOR BLOWPT COOKTIME
0:00 0.63457 NOBLOW
0:01 1.23741 NOBLOW
0:02 2.37568 NOBLOW
0:03 4.79514 NOBLOW
0:04 6.12894 NOBLOW
0:05 12.29853 NOBLOW
0:06 39.79284 BLOW
0:07 39.79284 BLOW
0:08 39.79284 BLOW
0:09 0.76581 NOBLOW
0:10 1.45827 NOBLOW
0:11 3.78290 NOBLOW
0:12 11.73659 NOBLOW
0:13 32.48762 NOBLOW
0:14 49.31267 BLOW
0:15 49.31267 BLOW

Essentially, I need a formula that will count "A" when "B" is >5, until C="BLOW" per each cycle, as there will be around 60

If there's any possible way you could help me, or if you think there is an easier way to do this, I'd be incredibly grateful.

Thanks for your time.

Andrew

 

[Robert Mika]

Hey everyone. I'm currently a student and my studies have nothing to do with excel, but I have am in the middle of an internship where all I do is work with spreadsheets. Currently, I'm working with a paper company in Alabama, where they asked me to create a spreadsheet that measures efficiency. While I've made great progress on the project, I just came up on a little snag and was wondering if you might be able to help.

I was asked to calculate what is known as "cooktime" per every cycle (each cycle culminates in what is known as "blowtime". "Cooktime" refers to the total number of minutes measured from when pressure reaches a certain point until the blowtime.

A simple version of what I'm trying to do looks like this (Same example in .xls attachment):


A B C D
TIME HFACTOR BLOWPT COOKTIME
0:00 0.63457 NOBLOW
0:01 1.23741 NOBLOW
0:02 2.37568 NOBLOW
0:03 4.79514 NOBLOW
0:04 6.12894 NOBLOW
0:05 12.29853 NOBLOW
0:06 39.79284 BLOW
0:07 39.79284 BLOW
0:08 39.79284 BLOW
0:09 0.76581 NOBLOW
0:10 1.45827 NOBLOW
0:11 3.78290 NOBLOW
0:12 11.73659 NOBLOW
0:13 32.48762 NOBLOW
0:14 49.31267 BLOW
0:15 49.31267 BLOW

Essentially, I need a formula that will count "A" when "B" is >5, until C="BLOW" per each cycle, as there will be around 60

If there's any possible way you could help me, or if you think there is an easier way to do this, I'd be incredibly grateful.

Thanks for your time.

Andrew

Hallo and welcome.
How long is one cycyle?
in this example to result will in each BLOW row in column C?
Could you give an example of your result?

 

[AJRIGNEY]

Thanks for responding, Robert. There is actually no standard duration for each cycle, which makes it slightly more challenging.

Let me try to break it down:

CYCLE STARTS:"NOBLOW" HAS AN HFACTOR > 5
CYCLE ENDS: THE "NOBLOW" IMMEDIATELY BEFORE THE SUBSEQUENT "BLOW"

I need the total number of minutes in each cycle, but as I noted earlier, that sum will vary (as it is dependent on the HFactor, or pressure).

In the example, there should be 2 cycles each with a duration of 2 minutes.

 

[Robert Mika]

Would this work for you?

Excel Workbook
	A	B	C	D	E	F	G	H
1	TIME	HFACTOR	BLOWPT	COOKTIME	*	*	Cycle	*
2	00:00	0.63457	NOBLOW	*	*	*	1	00:36
3	00:01	1.23741	NOBLOW	*	*	*	2	02:00
4	00:02	2.37568	NOBLOW	*	*	*	*	*
5	00:03	4.79514	NOBLOW	*	*	*	*	*
6	00:04	6.12894	NOBLOW	*	*	*	*	*
7	00:05	12.29853	NOBLOW	*	*	*	*	*
8	00:06	39.79284	BLOW	*	*	*	*	*
9	00:07	39.79284	BLOW	*	*	*	*	*
10	00:08	39.79284	BLOW	00:36	*	*	*	*
11	00:09	0.76581	NOBLOW	*	*	*	*	*
12	00:10	1.45827	NOBLOW	*	*	*	*	*
13	00:11	3.7829	NOBLOW	*	*	*	*	*
14	00:12	11.73659	NOBLOW	*	*	*	*	*
15	00:13	32.48762	NOBLOW	*	*	*	*	*
16	00:14	49.31267	BLOW	*	*	*	*	*
17	00:15	49.31267	BLOW	02:00	*	*	*	*
Sheet1

 

[AJRIGNEY]

Sorry Robert, but that's not quite what I need. I've been working on the formula with one of my programming friends, and we were close to coming up with a solution but then we hit another snag. I wish I could add an attachment, but the formula for D2 is this:

{=IF(AND(C3=$I$3,C4=$J$3),COUNTIF(B3:INDEX(B$2:$B3,IF(ISNA(MATCH(1,1/(C$2:$C3=$J$3))+1),1,MATCH(1,1/(C$2:$C3=$J$3))+1)),$I$2),"")}

Unfortunately, when I plug in this formula to my spreadsheet, it works only for the first cycle. The next 3-4 cycles are off by one, and after that, the sum continues to increase in error (margin of error eventually reaches 1962 when I get around row 10,000.

Unfortunately, I can't show you what I mean without an attachment or a link. I tried uploading through google docs, but that screwed up the data.