Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Aug 2011
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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),"")

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Aug 2011
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average or occurrences help needed

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

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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),""))

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Aug 2011
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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 ?
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    546
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  8. #8
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    Last edited by Aryatect; Aug 17th, 2019 at 01:41 AM.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •