Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bec" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
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 "Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bec" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bec" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
30, 60 or 90 day averages | Excel Worksheet Functions | |||
Averages | New Users to Excel | |||
Averages | Excel Worksheet Functions | |||
Averages | Excel Worksheet Functions | |||
Averages | Excel Worksheet Functions |