Converting Half-hourly data into average daily data

[TommyHayes1989]

Hi guys

I have a very very long list of variables, one is electricity output, which is measured every half hour. I need to convert this to daily, so obviously summing up and dividing by 48 (24 x half hours). I was just wondering if there is a quick drag option that will put blocks of 48 into one separate cell for me? I have attached the problem.
So for example C2:C49 is 1st Jan 2013, to get the daily average I have added up and divided by 48, which is in O2. But I can't click and drag down because I can't have Jan 1st and Jan 2nd combining, which would be O3 if I did. I can't do this manually because it would take ages. I have attached the document
What I need is O2 = daily average 1st Jan
O3 = daily average 2nd Jan...etc

[Fermented Reptile]

Hi there,

As you're using Excel 2010 you can use AVERAGEIF. So, for the attached sheet this should work:

=AVERAGEIF($A$2:$C$2833,$N2,$C$2:$C$2833)

HTH,

FermentedR

[TommyHayes1989]

Thank you,

so if i drag that down I should get the averages on a daily basis? in accordance with the dates in column N?

[Fermented Reptile]

You're welcome! Yes, just drag it down, should work fine

[TommyHayes1989]

ok i have more than one of these documents. I have six months ones, same problem
what is the standard formula?

[Fermented Reptile]

Take a look at this - should be able to surmise from this article and what we've already gone through: http://office.microsoft.com/en-gb/ex...010342193.aspx

[TommyHayes1989]

Hi there, thanks again. but i just found out that I need the sum of the total daily, rather than the average. what would be the answer for that? sorry

[avtr]

Hello, this query is pretty much exactly like mine but with a slight twist. First of all the function here for summing daily would be SUMIF but I'm sure you got this already.

I have a data set which contains half hourly reads but for several customers. so for January, and 30 customers, there are 48 (albeit missing data) rows with the same customer ID for 1st, then another 48 rows for 2nd unto 31st. Then of course same for all customers.

Would there be a way of calculating the daily sum as before but for each customer? I am looking at several hundreds of customers, so this will be very useful!

[avtr]

Thus is similar to the post: Re: Converting Half-hourly data into average daily data

I have a data set which contains half hourly reads but for several customers. so for January, and 30 customers, there are 48 (albeit missing data) rows with the same customer ID for 1st, then another 48 rows for 2nd unto 31st. Then of course same for all customers.

Would there be a way of calculating the daily sum as before but for each customer? I am looking at several hundreds of customers, so this will be very useful!

[FDibbins]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.