Forum Discussion
Averaging values as a function of milliseconds of time to seconds of time
I have data that was created every 100 or so milliseconds over 5 minutes (about 3k data points).
I want to average all data within a length of a second into a single data point.
here is an example of the raw data vs the desired data:
There isn't a consistent amount of data points for each second (sometimes 9 sometimes 10).
Can this be done in excel?
Solution:
Highlight your data and make it into a table (Ctrl T) > Data > From Table (Above Get & Transform)
With the Time column selected, Transform > Extract > First characters > 8 > OK
With the Value column Selected > Transform > Group By > Group by: Time, New Column Name: Average, Operation: Average, Column: Value > OK
Home > Close & Load
Now, all that you have to do is add new data to the bottom of your table and click refresh then your PQ table will be updated.
2 Replies
Solution:
Highlight your data and make it into a table (Ctrl T) > Data > From Table (Above Get & Transform)
With the Time column selected, Transform > Extract > First characters > 8 > OK
With the Value column Selected > Transform > Group By > Group by: Time, New Column Name: Average, Operation: Average, Column: Value > OK
Home > Close & Load
Now, all that you have to do is add new data to the bottom of your table and click refresh then your PQ table will be updated.Hi Eitan,
That could be like
=SUMPRODUCT((VALUE(LEFT(A:A,8))=D2)*B:B)/SUMPRODUCT(--(VALUE(LEFT(A:A,8))=D2))
