ExcelBanter - reduce data points and average?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - reduce data points and average? (https://www.excelbanter.com/excel-worksheet-functions/446022-reduce-data-points-average.html)

reduce data points and average?

hello all, here's the isse:

column A : time, formatted in dd/mm/yyyy mm:ss
column B : a numerical value (3.30, 3.31...)

As you can see below I have 6 of these numerical value entry per minute. I would like to reduce them to 1 entry per minute, and this entry should be the average of the 6 ; what do you recommend? thank you!!

07/05/2012 00:01 3.30
07/05/2012 00:01 3.31
07/05/2012 00:01 3.33
07/05/2012 00:01 3.33
07/05/2012 00:01 3.33
07/05/2012 00:01 3.31
07/05/2012 00:02 3.34
07/05/2012 00:02 3.37
07/05/2012 00:02 3.36
07/05/2012 00:02 3.34
07/05/2012 00:02 3.34
07/05/2012 00:02 3.32
07/05/2012 00:03 3.32
07/05/2012 00:03 3.28
07/05/2012 00:03 3.28
07/05/2012 00:03 3.31
07/05/2012 00:03 3.32
07/05/2012 00:03 3.29
07/05/2012 00:04 3.29
07/05/2012 00:04 3.31
07/05/2012 00:04 3.32
07/05/2012 00:04 3.29
07/05/2012 00:04 3.29
07/05/2012 00:04 3.29
07/05/2012 00:05 3.27
07/05/2012 00:05 3.29
07/05/2012 00:05 3.31
07/05/2012 00:05 3.31
07/05/2012 00:05 3.32
07/05/2012 00:05 3.30

reduce data points and average?

In C1 enter this formula and drag/copy down until you get an error.

=AVERAGE(INDEX(B:B,ROW(B1)*6-5):INDEX(B:B,ROW(B1)*6))

If you don't have even sets of 6 times it won't give you proper
results.

Gord

On Thu, 10 May 2012 00:17:36 +0000, contrucci
wrote:

hello all, here's the isse:

column A : time, formatted in dd/mm/yyyy mm:ss
column B : a numerical value (3.30, 3.31...)

As you can see below I have 6 of these numerical value entry per minute.
I would like to reduce them to 1 entry per minute, and this entry
should be the average of the 6 ; what do you recommend? thank
you!!

07/05/2012 00:01 3.30
07/05/2012 00:01 3.31
07/05/2012 00:01 3.33
07/05/2012 00:01 3.33
07/05/2012 00:01 3.33
07/05/2012 00:01 3.31
07/05/2012 00:02 3.34
07/05/2012 00:02 3.37
07/05/2012 00:02 3.36
07/05/2012 00:02 3.34
07/05/2012 00:02 3.34
07/05/2012 00:02 3.32
07/05/2012 00:03 3.32
07/05/2012 00:03 3.28
07/05/2012 00:03 3.28
07/05/2012 00:03 3.31
07/05/2012 00:03 3.32
07/05/2012 00:03 3.29
07/05/2012 00:04 3.29
07/05/2012 00:04 3.31
07/05/2012 00:04 3.32
07/05/2012 00:04 3.29
07/05/2012 00:04 3.29
07/05/2012 00:04 3.29
07/05/2012 00:05 3.27
07/05/2012 00:05 3.29
07/05/2012 00:05 3.31
07/05/2012 00:05 3.31
07/05/2012 00:05 3.32
07/05/2012 00:05 3.30

reduce data points and average?

contrucci pretended :
hello all, here's the isse:

column A : time, formatted in dd/mm/yyyy mm:ss
column B : a numerical value (3.30, 3.31...)

As you can see below I have 6 of these numerical value entry per minute.
I would like to reduce them to 1 entry per minute, and this entry
should be the average of the 6 ; what do you recommend? thank
you!!

07/05/2012 00:01 3.30
07/05/2012 00:01 3.31
07/05/2012 00:01 3.33
07/05/2012 00:01 3.33
07/05/2012 00:01 3.33
07/05/2012 00:01 3.31
07/05/2012 00:02 3.34
07/05/2012 00:02 3.37
07/05/2012 00:02 3.36
07/05/2012 00:02 3.34
07/05/2012 00:02 3.34
07/05/2012 00:02 3.32
07/05/2012 00:03 3.32
07/05/2012 00:03 3.28
07/05/2012 00:03 3.28
07/05/2012 00:03 3.31
07/05/2012 00:03 3.32
07/05/2012 00:03 3.29
07/05/2012 00:04 3.29
07/05/2012 00:04 3.31
07/05/2012 00:04 3.32
07/05/2012 00:04 3.29
07/05/2012 00:04 3.29
07/05/2012 00:04 3.29
07/05/2012 00:05 3.27
07/05/2012 00:05 3.29
07/05/2012 00:05 3.31
07/05/2012 00:05 3.31
07/05/2012 00:05 3.32
07/05/2012 00:05 3.30

<FWIW
This looks very much like a custom report generated by data logger
software. If so, that same software should be able to do this
(depending on the quality of the data logger product). If it's raw data
then the controller should be able to be reconfigured to generate
avg/min!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

reduce data points and average?

Gord,
As per my reply to OP, if this data is/was generated by a data logger
then the frequency will be consistent for the runtime of the logging
session.

contrucci,
Gord's formula will most likely not encounter an error unless the end
data was cut short of a full cycle. If this happens then simply discard
the errant data. (This would be 5 or less rows at this output
frequency)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

reduce data points and average?

Thanks Gary

Gord

On Thu, 10 May 2012 08:42:04 -0400, GS wrote:

Gord,
As per my reply to OP, if this data is/was generated by a data logger
then the frequency will be consistent for the runtime of the logging
session.

contrucci,
Gord's formula will most likely not encounter an error unless the end
data was cut short of a full cycle. If this happens then simply discard
the errant data. (This would be 5 or less rows at this output
frequency)