# Problems with working out averages with large amounts of data

1. ## Problems with working out averages with large amounts of data

I have a problem with working out averages that hopefully someone one here
can help with.

Please bear with me as i try to explain. Here goes: I currently have a
worksheet that contains two columns, column A has the date and time for data entries over a year. In each day there are a varying amount of data entries. Column B is the data entry for each given time.

for example

A1 = Date/Time B1 = data
A2 = 24/01/2007 17:15 B2 = 1.5
A3 = 24/01/2007 18:00 B3 = 1.2
A26 = 28/01/2007 19:00 B26 =1.8
A245 =04/06/2007 09:15 B245 = 0.6
and so on....

Now the problem is, i need to work out a daily average for each day of data i have but i have over a full years worth of data in this format and each day has varying amounts of data entries ranging from 10 entries to over 30. The way i started to address this problem was to set up an average function of all the results for each day but as i have found out the function cannot be copied as each day has a different amount of readings and therefore the data series for each function changes per day. Doing it this way means creating 365+ functions and then going through each forumla to check that the data series covers the correct data entries. Due to the fact i have over 39000 individual data entries this takes hours and i hope there is someone out there who can suggest a quicker way of doing this?

is there any way i can set up a function to isolate entries per day and then
average them?

many thanks in advance to anyone who can help

Mindblank

2. Hello Mindblank:

This will sum all the data in column B that are equal to the month number in cell C2 and the day number in cell D2.

``Please Login or Register  to view this content.``
This will count all the days in column A that are equal to the month number in cell C2 and the day number in cell D2.

``Please Login or Register  to view this content.``

3. Another option might be:

In C1 type: Date
In D1 type: Average
In C2 type: 1/1/2007
Fill that down 365 rows so you have 1 row for each day of the year
In D2 use:
``Please Login or Register  to view this content.``
This is an array formula, so after typing or modifying it you must press CTRL+SHIFT+ENTER. Change your ranges (A2:A1000, B2:B1000 to suit your actual data range.

The formula will return a blank if no values existed for that date, otherwise it will return the average for just that particular day. Fill that formula down to D366 (so you have a blank or a value for each date). Now just apply an AutoFilter to columns C/D and in column D select the drop-down arrow and choose 'Non-blanks'

I'm sure there's many other ways, too.

4. thanks very much for your help guys, got it working now, saved many many hours of laborius work.

thanks again

Mindblank

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1