# Thread: Average or occurrences help needed Thanks: 0 Likes: 0

1. ## Average or occurrences help needed

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 occurrence GPM Desired output occurrence average gpm 1 175.3245 1 174.6 1 175.6294 2 152.43 1 173.9357 3 1 174.1177 4 1 174.2998 5 1 174.4818 1 174.6638 1 173.6071 1 174.2018 1 176.7737 1 174.1055 1 173.8339 1 175.6286 1 173.8325 0 0 0 0 0 0 0 0 0 0 2 24.56381 0 2 1.924029 0 0 2 14.86348 2 1.62 2 3.057903 2 3.343711 2 14.5067 2 12.77087 2 3.227844 2 114.8468 2 197.5547 2 244.9118 2 266.7711 2 274.3633 2 283.6748 2 285.0492 2 296.478 2 313.9584 2 338.4923 2 352.7092  Reply With Quote

2. ## Re: Average or occurrences help needed

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

DE
1Desired output
2occurrenceaverage gpm
31174.6026
42152.4344
53
64
75

Sheet1

Worksheet Formulas
CellFormula
E3=IFERROR(AVERAGEIFS(\$B\$2:\$B\$48,\$A\$2:\$A\$48,D3),"")  Reply With Quote

3. ## Re: Average or occurrences help needed

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

4. ## Re: Average or occurrences help needed

Try below:

DEF
1Desired output
2occurrenceaverage gpm
31174.6026174.6
42152.4344152.43
5
6
7
8
9

Sheet1

Worksheet Formulas
CellFormula
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),""))  Reply With Quote

5. ## Re: Average or occurrences help needed

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  Reply With Quote

6. ## Re: Average or occurrences help needed

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 ?  Reply With Quote

7. ## Re: Average or occurrences help needed

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  Reply With Quote

8. ## Re: Average or occurrences help needed

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

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

Worksheet Formulas
CellFormula
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  Reply With Quote

## User Tag List

average, gpm, occurrence, occurrences, pump 