Results 1 to 6 of 6

Thread: I need help with Averageifs in VBA or formula

  1. #1
    Seeker abouya's Avatar
    Join Date
    Dec 2016
    Posts
    19
    Articles
    0
    Excel Version
    2016

    Question I need help with Averageifs in VBA or formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I'm looking to calculate the average A2 to lastRow for every 30 minutes (30 Rows) only if the status says: Good in Column C and the first range to start with has to be 00mins. for example to start the first 30 rows average at 9:00.
    The values that need to be averaged are in column B.

    I'm not sure what my best route. VBA seems to be complicated using averageif formulas. I attempted to calculate average for each 30 rows but i had to manually specify the last column. Also, I was able to do some calculations to figure out where to start 00mins sharp but how to include it in the formula.

    I'm looking to calculate 30mis average and 24 averages


    I would really appreciate any input /help I can get.

    Thank you



    Book1.xlsx

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,822
    Articles
    0
    Excel Version
    O365
    Post an example workbook and some example results please.

  3. #3
    Seeker abouya's Avatar
    Join Date
    Dec 2016
    Posts
    19
    Articles
    0
    Excel Version
    2016

    Post

    Quote Originally Posted by Bob Phillips View Post
    Post an example workbook and some example results please.
    Hello Bob,

    I appreciate your request. I hope this file makes it easier to understand what I'm looking to do.

    Thanks,



    DATA Averages.xlsx

  4. #4
    Seeker abouya's Avatar
    Join Date
    Dec 2016
    Posts
    19
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Bob Phillips View Post
    Post an example workbook and some example results please.
    Hello,

    I keep trying new formulas but it's hard to accomplish the task. Any help will be very appreciated.

    Best,

    DATA Averages.xlsx

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,822
    Articles
    0
    Excel Version
    O365
    Formula solution.

    In F3, enter

    =MATCH(E3,A:A,0)-30

    In G3, enter this array formula

    =IF(COUNTIF(INDEX(C:C,F3,0):INDEX(C:C,F3+29,0),"Bad")>6,"Not good",AVERAGE(IF(INDEX(C:C,F3,0):INDEX(C:C,F3+29,0)="Good",INDEX(B:B,F3,0):INDEX(B:B,F3+29,0))))

  6. #6
    Seeker abouya's Avatar
    Join Date
    Dec 2016
    Posts
    19
    Articles
    0
    Excel Version
    2016

    Smile

    Quote Originally Posted by Bob Phillips View Post
    Formula solution.

    In F3, enter

    =MATCH(E3,A:A,0)-30

    In G3, enter this array formula

    =IF(COUNTIF(INDEX(C:C,F3,0):INDEX(C:C,F3+29,0),"Bad")>6,"Not good",AVERAGE(IF(INDEX(C:C,F3,0):INDEX(C:C,F3+29,0)="Good",INDEX(B:B,F3,0):INDEX(B:B,F3+29,0))))

    Thank you so much. Your formula works great! I did create another column for the rows so the formula can do its work. I created a pivot table to compare and the averages are accurate. I really appreciate it.

    I followed the same to calculate 24hr averages that starts from hours 00:00 and it didn't work. I'm posting the previous solution and my attempt to calculate the 24hr with conditions.

    Thanks again and best regards,


    DATA Averages 24hr.xlsx

Posting Permissions

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