Average every 24 rows

  • Thread starter Thread starter beek
  • Start date Start date
B

beek

Hi,
I have large data sets with hourly values and want to calculate daily,
weekly and monthly averages.
For example: average C4 - C27, average C28 - C51, ..... - C43827
I tried:

=AVERAGE(INDIRECT("c"&(ROW(C4)-1)*24+4&":c"&ROW(C4)*24+4))

but that didn't work.

Does anybody know a solution?

Thanks a lot!

Tim
 
Pivot Table?? As long as you have Dates in a column in your data, then you will
have 24 values for each date. Stick it all in a Pivot table and you will have
all the daily averages you like, in a very nice looking report. You can then
group your dates to get weekly or monthly data as well with just a few
mouseclicks.

If you have a zipped dataset you can send out, I'll happily do a few up for you
to show you what you could get.
 
But if you really want a formula, then with your data in the ranges indicated,
starting in say G4 put this formula and copy down:-

=AVERAGE(OFFSET(INDIRECT("C"&((ROW()-4)*24)+4),,,24))

If you started in G3 with the formula make it

=AVERAGE(OFFSET(INDIRECT("C"&((ROW()-3)*24)+4),,,24))

If you started in G2 with the formula make it

=AVERAGE(OFFSET(INDIRECT("C"&((ROW()-2)*24)+4),,,24))

If you started in G1 with the formula make it

=AVERAGE(OFFSET(INDIRECT("C"&((ROW()-1)*24)+4),,,24))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Ken Wright said:
Pivot Table?? As long as you have Dates in a column in your data, then you will
have 24 values for each date. Stick it all in a Pivot table and you will have
all the daily averages you like, in a very nice looking report. You can then
group your dates to get weekly or monthly data as well with just a few
mouseclicks.

If you have a zipped dataset you can send out, I'll happily do a few up for you
to show you what you could get.
Click to expand...
 
OK:-

First off, put headers over your data (MUST have headers), and then insert a
column between your dates and values and head it 'Week'

You will now have in A3 'Date', in B3 'Week', in C3 'Num', in D3 'PPTPRI01'

In cell B3 put =WEEKNUM(A3) and copy down. The data will look like dates, so
format the column as numbers with no decimal places.

Select the entire dataset, ie with your example A3:D1468 and do Data / Pivot
Table and Chart report

Hit Next / Next / Finish

Drag Date to the left where it says ROW fields, and then drag PPTPRI01 into the
DATA area and right click on one of the numbers and hit 'field settings' and
then select 'Average' from the list you will see. You now have averages by Day.

Right click on any of the dates and choose Group and show detail / Group and
then you will probably find Months is already selected, so just also click on
years. Hit OK and you will now have your averages by month.

Right click anywhere on the table and choose 'Show field list' and then drag
'Week' BETWEEN the months and the Data area. You now have averages by week.

If you want you can create 3 Pivot tables from the data to show you each report
separately.
 
Thank you very much Ken!
It finally works and saves me a lot of time.

All the best to you mate,

Tim
 
Back
Top