Average daily values

[damo_uk]

Dear all,

I have a table of data covering the last 9 months based on values automatically collated from 15 minute intevals.
The date/time is in column A (01/01/2009 00:00) with the data collected in column D.

My wish is to get the average daily data from column D and I am slowly losing my head!!!

Is there anyway of getting a formula to auto-average the daily values bearing in mind there are currently 96 daily entries.

I have tried converting the first 5 digits of column A to numeric (i.e. 31894 for 01/01) then trying to write a formula saying =average(D1:D24577,if(range="31894",1)).

I can now see a simpler way but am so confused after an hour or so of trying.

Each day has 96 readings so I need an auto adding formula. average column cell A would say =average(D1:D96).
Is there are way to have the cell below auto-update itself to look at the next 96 values and so on and so forth?

Sorry if none of this makes sense!

Damien

[Andy Pope]

One way would be to use SUMIF and COUNTIF to calculate the average.

[damo_uk]

Wow,

Thank you so much. Have done it straight away and it worked a charm.

I can remember doing somthing before based on 15 min data and using 96 in the formula to get average day but that was a few years back and I cannot for the life of me remember.

Cheers for writing back so quick.

All done now and can head of home soon! :-)

Damien

[damo_uk]

Andy,

Thank you for you help earlier.
I have recreated the formula you used into my sheet, however I seem to be having one or two discrepancies between the sumif/countif column and actual average value.

I have attached the spreadsheet to this note (not sensitive information). If you could spare 30 seconds, could you have a quick look at my formula and let me know if you can see what I have done wrong. If you can't see anything, I guess I'll have to resort to doing it manually?

Many thanks,

Damien

*edit - I just noticed it is including blank cells (of which I have many) in the average hence pulling it down. I will attempt to update the formula to get it to minus 'countblank' from the 'countif' however I do not have time this evening.
I will leave this post open for now and close it when I have solved it tomorrow.

[DonkeyOte]

I would suggest you use a Pivot Table - you only need columns A & D, you can set Date as ROW Field and Values as DATA Field set to Average - you can Group the Date field by Day such that the rows are condensed on a daily basis.

for more info. on grouping see http://www.contextures.com/xlPivot07.html

[damo_uk]

Thank you DonkeyOte,

I have never used a Pivot Table and always wanted to know how. That link helps, however...

When I try and add the date to the row field - I get the error message "A field in your source data has too many unique items that cannot be used in a pivot table". The date field has 24577 entries.

I am using Excel 2000 and not sure if this is why it can't handle it?

Damien

[DonkeyOte]

There is a limit on the number of unique items you can have in a PT Row Field in 2000 see: http://support.microsoft.com/default...23120121120120

I suspect having seen your original that this is because you have 96 rows per day... if that's the case then I suspect in terms of "unique" dates there are only around 256.

On that basis if you do what you did before in terms of creating a date only column and use this as your Row Field you should be ok, eg if A2 onwards contains DateTime values then

B2: =INT(A2)
copied down

and use B as your ROW field

(this also negates need for Grouping as this is being done by the formula in B already)

[damo_uk]

Thank you DonkeyOte,

I had literally just done this when you replied!

As I had already created a row of date only (as numeric using =left,5 to get the first half of date), I used this instead as it's all I'm interested in.
It created the pivot straight away and I changed the function of the data to be the average and the first 10 results I have hand checked and are correct.

Many thanks for all the help.

Damien

[DonkeyOte]

Just to clarify re: Dates / Times etc...

In XL Dates are Integers and Time is always Decimal - ie noon is 0.5 as 6am is 0.25 and 6pm is 0.75... thus if you ever need to extract just the Date portion from a DateTime value you can use INT function

=INT(NOW())

returns just the date... to do the opposite you can use MOD

=MOD(NOW(),1)

returns just the time [as would NOW()-INT(NOW())]

(obviously use of NOW() is just to demo)

Using

=LEFT(NOW(),5)

is open to error if the date is early (1920s etc) ... it would also return the date integer as a text string rather than as a number... to revert to number you would need to coerce via standard arithmetic operator, eg:

=0+LEFT(NOW(),5)

[damo_uk]

Ah, that's very interesting and wise to know!

I have managed to do it for now with my data set but will use that in future. Have had trouble with dates in the past so INT will definately be used!!!

Thank you for all your help and info!

FYI: Having researched this in Google, I noticed Excel 2007 actually has an averageif function! Would have saved me a lot of time in the first instance if work would allow users to upgrade from 2000!

Damien

[DonkeyOte]

I noticed Excel 2007 actually has an averageif function!

It even has AVERAGEIFS which can be even handier...that said the advantages for your users would probably be offset by the hair pulling that goes with the very different UI (the dreaded Ribbon)

[damo_uk]

That said the ribbon can't be any worse than our outdated (by about 8 years) Citrix Office system which twice kicked my off the system yesterday losing all my work (about 5 hours worth in total).

Even worse is the IT services recommendation to 'continually save' which when I use Excel for anything more complex than viewing data (i.e. using formulas (even just sum) or changing a font size/colour) - shuts it down!

The joys of my work!