Averaging rows by irregular values in Column X

**FDibbins** · 12-04-2014, 03:17 PM

Hi, welcome to the forum

Without adding a helper (which would make it MUCH simpler), try this ARRAY formula, copied down...
=SUMPRODUCT(IF(ROUND($F$2:$F$326,5)=ROUND(F2,5),$C$2:$C$326))/SUMPRODUCT(COUNT(IF(ROUND($B$2:$B$326,5)=ROUND(B2,5),$B$2:$B$326,"")))
If you want to only show the 1st instance of a series, use this from the SECOND row down (if you put it inthe 1st row, it will give an error)...
=IF(ROUND(B2,5)=ROUND(B1,5),"",SUMPRODUCT(IF(ROUND($F$2:$F$326,5)=ROUND(F2,5),$C$2:$C$326))/SUMPRODUCT(COUNT(IF(ROUND($B$2:$B$326,5)=ROUND(B2,5),$B$2:$B$326,""))))

However, if you are able to add a helper column, use this (I used D), then put this in D2...
=ROUND(B2,5)
and then put this in E2...
=IF(D2=D1,"",AVERAGEIF($D$2:$D$326,D2,$C$2:$C$326))
both copied down

**MaverickBlack** · 12-04-2014, 04:09 PM

Hi FDibbins and thanks for the help!

I added in the helper column, but using the Round function on time values was throwing things off (with conversion of base 10 numbers to time being somewhat convoluted). I changed it to an MROUND(B2,0.00001157407) to make it round to the whole second and it works great!

Also, is there an easy way to go about condensing the rows and outputing to a new worksheet? Adding additional columns is less of an issue with my HK files but .simdata csv files have a large number of columns that I will need to get an average for.

Thanks again!