Average or occurrences help needed

[maximus315]

I need to quickly evaluate pump rates.

I have an output (pump gpm) that gets recorded to excel every minute in a new row. The pump turns on an off multiple times a day depending on build up. What I want is to know the number of occurrences a day that the pump turns on and the average gpm during these time frames (the pump on time for each event also varies) and sometimes there is blips at startup of an occurrence.


Occurrences were added in the table by hand in the example below.






[TABLE="width: 409"]
<tbody>[TR]
[TD][/TD]
[TD]Data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]occurrence[/TD]
[TD]GPM[/TD]
[TD][/TD]
[TD] Desired output[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]occurrence[/TD]
[TD]average gpm[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]175.3245[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]174.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]175.6294[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]152.43[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]173.9357[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]174.1177[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]174.2998[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]174.4818[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]174.6638[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]173.6071[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]174.2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]176.7737[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]174.1055[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]173.8339[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]175.6286[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]173.8325[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]24.56381[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]1.924029[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]14.86348[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]1.62[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]3.057903[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]3.343711[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]14.5067[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]12.77087[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]3.227844[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]114.8468[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]197.5547[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]244.9118[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]266.7711[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]274.3633[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]283.6748[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]285.0492[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]296.478[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]313.9584[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]338.4923[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]352.7092[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Hi, Below should work if you data range header is in A1 & B1 and data starting in A2&B2:

Book1
	D	E
1	Desired output
2	occurrence	average gpm
3	1	174.6026
4	2	152.4344
5	3
6	4
7	5
Sheet1

Cell Formulas
Range	Formula
E3	=IFERROR(AVERAGEIFS($B$2:$B$48,$A$2:$A$48,D3),"")

 

[maximus315]

I need help having Excel identify the occurrences (instead of putting them in by hand)

 

[Aryatect]

Try below:

Book1
	D	E	F
1	Desired output
2	occurrence	average gpm
3	1	174.6026	174.6
4	2	152.4344	152.43
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
D3	=IFERROR(INDEX($A$2:$A$48,AGGREGATE(15,6,(ROW($A$2:$A$48)-ROW($A$2)+1)/(MATCH($A$2:$A$48,$A$2:$A$48,0)=(ROW($A$2:$A$48)-ROW($A$2)+1)),ROWS(D$3:D3))),"")
E3	=IF(D3="","",IFERROR(AVERAGEIFS($B$2:$B$48,$A$2:$A$48,D3),""))

 

[maximus315]

Sorry I was not clear,

I am trying to fill in column A to start with - the only data at the start is in column B (the GPM)
I want excel to determine the occurrences (pump start to stop is 1 occurrence)

thanks for the help

 

[Aryatect]

So in the example you gave, occurrences should be 4 right row 2 to row 15 - 1st , then row 26 - 2nd occurrence , then there is a gap of row 27 and hence row 28 - 3rd occurrence and row 31 to 48 is 4th ?

 

[Dr. Demento]

Not sure how accurate you need to be with your average calculation, but to average rates you need to use the harmonic mean not arithmetic mean.

https://www.comap.com/FloydVest/Course/PDF/Cons25PO.pdf
https://en.wikipedia.org/wiki/Harmonic_mean

 

[Aryatect]

if the understanding from post #6 is correct then below should work (Showing only part of data):

Book1
	A	B	C	D	E
1	Occurrence	GPM		Desired output
2	1	175.3245		occurrence	average gpm
3	1	175.6294		1	174.6026
4	1	173.9357		2	24.56381
5	1	174.1177		3	1.924029
6	1	174.2998		4	167.9
7	1	174.4818
8	1	174.6638
9	1	173.6071
10	1	174.2018
11	1	176.7737
12	1	174.1055
13	1	173.8339
14	1	175.6286
15	1	173.8325
16		0
17		0
18		0
19		0
20		0
21		0
22		0
23		0
24		0
25		0
26	2	24.56381
27		0
28	3	1.924029
Sheet1

Cell Formulas
Range	Formula
A3	=IF(B3>0,IF(A2="",MAX(A$2:A2)+1,MAX(A$2:A2)),"")
D3	=IFERROR(INDEX($A$2:$A$48,AGGREGATE(15,6,(ROW($A$2:$A$48)-ROW($A$2)+1)/(MATCH(($A$2:$A$48)*($A$2:$A$48<>""),$A$2:$A$48,0)=(ROW($A$2:$A$48)-ROW($A$2)+1)),ROWS(D$3:D3))),"")
E3	=IF(D3="","",IFERROR(AVERAGEIFS($B$2:$B$48,$A$2:$A$48,D3),""))

Putting down 1 manually in A2
Copy formula from A3 down
Copy formula from D2 down
Copy formula from E2 down