Averages

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bec - 11 Sep 2007 06:50 GMT

Hi have a spreadsheet which has lines for every minute of the day

20/8/07 1900
20/8/07 1901 etc etc for a number of weeks.

How do I easily find the average of each 15 minute period without having to
copy and paste an average formula every 15 lines? and so it's in one table
(ie no gaps)

TIA
Bec

Reply to this Message

Max - 11 Sep 2007 07:26 GMT

Assuming the values to be averaged are running in C1 down

Put in D1:
=AVERAGE(OFFSET(C$1,ROWS($1:1)*15-15,,15))
Copy D1 down as far as required

D1 returns the average of C1:C15
D2 returns the average of C16:C30, and so on, as desired

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi have a spreadsheet which has lines for every minute of the day
>
[quoted text clipped - 7 lines]
> TIA
> Bec

Reply to this Message

Bec - 12 Sep 2007 04:44 GMT

Hi Max

Thanks for the formula. I was able to make it work using columns not rows.
How do I make the starting value change every 15 cells automatically? My
spreadsheet is set out like below.

TIA
Bec

20/08/2007 19:22    0.7950
20/08/2007 19:23    0.8090
20/08/2007 19:24    0.8600
20/08/2007 19:25    0.8600
20/08/2007 19:26    0.8420
20/08/2007 19:27    0.8500
20/08/2007 19:28    0.8450
20/08/2007 19:29    0.8520
20/08/2007 19:30    0.8540
20/08/2007 19:31    0.8470
20/08/2007 19:32    0.8480
20/08/2007 19:33    0.8510
20/08/2007 19:34    0.8480
20/08/2007 19:35    0.8580
20/08/2007 19:36    0.8560    0.8450
20/08/2007 19:37    0.8560
20/08/2007 19:38    0.8500
20/08/2007 19:39    0.8540
20/08/2007 19:40    0.8560
20/08/2007 19:41    0.8310
20/08/2007 19:42    0.8400
20/08/2007 19:43    0.8410
20/08/2007 19:44    0.8530
20/08/2007 19:45    0.8550
20/08/2007 19:46    0.8570
20/08/2007 19:47    0.8310
20/08/2007 19:48    0.8430
20/08/2007 19:49    0.8540
20/08/2007 19:50    0.8360
20/08/2007 19:51    0.7940    0.8434

> Assuming the values to be averaged are running in C1 down
>
[quoted text clipped - 15 lines]
> > TIA
> > Bec

Reply to this Message

Max - 12 Sep 2007 05:20 GMT

Thought you wanted all the results neatly bunched at the top, w/o any gaps ?
<g>
The earlier suggestion would do just that

Anyway, if you really want the results listed in the manner as per your
response below, where the values to be averaged are running in B1 down

Put in C1:
=IF(MOD(ROWS($1:1)-1,15)=14,AVERAGE(OFFSET(B1,,,-15)),"")
Copy C1 down as far as required. This will return the results exactly as
indicated in your response.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi Max
>
[quoted text clipped - 35 lines]
> 20/08/2007 19:50 0.8360
> 20/08/2007 19:51 0.7940 0.8434

Reply to this Message