# Thread: I need help with Averageifs in VBA or formula

1. ## I need help with Averageifs in VBA or formula

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. Post an example workbook and some example results please.

3. Originally Posted by Bob Phillips
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. Originally Posted by Bob Phillips
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. Formula solution.

In F3, enter

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

In G3, enter this array formula

6. Originally Posted by Bob Phillips
Formula solution.

In F3, enter

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

In G3, enter this array formula

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
•