Average or occurrences help needed

maximus315

New Member
Joined
Aug 2, 2011
Messages
7
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.






Data
occurrenceGPM Desired output
occurrenceaverage gpm
1175.32451174.6
1175.62942152.43
1173.93573
1174.11774
1174.29985
1174.4818
1174.6638
1173.6071
1174.2018
1176.7737
1174.1055
1173.8339
1175.6286
1173.8325
0
0
0
0
0
0
0
0
0
0
224.56381
0
21.924029
0
0
214.86348
21.62
23.057903
23.343711
214.5067
212.77087
23.227844
2114.8468
2197.5547
2244.9118
2266.7711
2274.3633
2283.6748
2285.0492
2296.478
2313.9584
2338.4923
2352.7092

<tbody>
</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi, Below should work if you data range header is in A1 & B1 and data starting in A2&B2:


Book1
DE
1Desired output
2occurrenceaverage gpm
31174.6026
42152.4344
53
64
75
Sheet1
Cell Formulas
RangeFormula
E3=IFERROR(AVERAGEIFS($B$2:$B$48,$A$2:$A$48,D3),"")
 
Upvote 0
Try below:


Book1
DEF
1Desired output
2occurrenceaverage gpm
31174.6026174.6
42152.4344152.43
5
6
7
8
9
Sheet1
Cell Formulas
RangeFormula
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),""))
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
if the understanding from post #6 is correct then below should work (Showing only part of data):


Book1
ABCDE
1OccurrenceGPMDesired output
21175.3245occurrenceaverage gpm
31175.62941174.6026
41173.9357224.56381
51174.117731.924029
61174.29984167.9
71174.4818
81174.6638
91173.6071
101174.2018
111176.7737
121174.1055
131173.8339
141175.6286
151173.8325
160
170
180
190
200
210
220
230
240
250
26224.56381
270
2831.924029
Sheet1
Cell Formulas
RangeFormula
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top