get average by date

[jrtaylor]

I've attached part of a very large worksheet (over 100,000 rows) in which column A are timestamps which have been converted from unix. The increments are very small, so in cases, it appears that row x is the same time as row x +1

I would like to get the average of column B for all "identical" times, then once I have this, get the average of column B for all times during every calendar day.

There is no strict pattern of the timestamps (that I can determine). But I'd like to come up with a method of giving equal weight to each minute of the day, and then in another column, get the average value for every 24 hour period.

I don't have a formula in mind for this "normalization" nor do I think I can manage the vlookup formula which I assume is required.

Any suggestions?

Thanks so much!

[AliGW]

Please add expected outcomes to the sample workbook (manually).

[jrtaylor]

I've attached a new file which shows an example of the manually calculated values I want to arrive at. I created several helper columns.

Column C gets the average of any values in column B which have the same timestamp in Column A.

Then in Column D I break a calendar day into 15 minute segments, then average these segments to obtain a daily value (column E).

I know that this project could be solved in other ways or with different granularity, but the main concern is that I do not want to wind up with a daily
value which uses 0's for time slots which have no value in column B. In other words, if there is no data for a particular minute, this does not mean that
we must assume that minute's value = 0.

(I believe that the average function does this automatically, by only averaging cells with values in a range)

Thanks!

[63falcondude]

Your desired results are incorrect. You are taking an average of an average (with unequal sizes).

The following formulas will produce the averages on the first instance of each desired value (instead of the last instance like in your sample):

C2 =IF(COUNTIF(A$2:A2,A2)=1,AVERAGEIF(A:A,A2,B:B),"")

E2 =IF(COUNTIF(A$2:A2,">="&INT(A2))=1,AVERAGEIFS(B:B,A:A,">="&INT(A2),A:A,"<"&INT(A2)+1),"")

Still working on column D.

[63falcondude]

These produce the results in the desired cells:

C2 =IF(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),AVERAGEIF(A:A,A2,B:B),"")

D2 =IF(COUNTIFS(A$2:A2,">="&FLOOR(A2,"00:15"),A$2:A2,"<="&CEILING(A2,"00:15"))=COUNTIFS(A:A,">="&FLOOR(A2,"00:15"),A:A,"<"&CEILING(A2,"00:15")),AVERAGEIFS(B:B,A:A,">="&FLOOR(A2,"00:15"),A:A,"<"&CEILING(A2,"00:15")),"")

E2 =IF(COUNTIF(A$2:A2,">="&INT(A2))=COUNTIFS(A:A,">="&INT(A2),A:A,"<"&INT(A2)+1),AVERAGEIFS(B:B,A:A,">="&INT(A2),A:A,"<"&INT(A2)+1),"")

[MarvinP]

Hey jr,

I'm thinking a Pivot Table where you can group by Date and Time AND do an Average might do what you need?? No formulas needed See the attached for the example.

Pivot Table Group by Minute Average.xlsx

[jrtaylor]

Thanks Marvin and Falcondude, I appreciate your help on this!

[63falcondude]

Glad we could help. Thanks for the rep!