I need help with Averageifs in VBA or formula

abouya

New member
Joined
Dec 16, 2016
Messages
19
Reaction score
0
Points
0
Excel Version(s)
2016
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



View attachment Book1.xlsx
 
Post an example workbook and some example results please.
 
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))))
 
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,


View attachment DATA Averages 24hr.xlsx
 
Back
Top