Data Table Manipulation

C

cmk18

I have a rather large data table (~6600 lines of data) that has severa
different locations, dates, times and then events for eac
Location/Time pair. For example, at Location 1 at 12:00 on 5/6/200
there were 5 events. I'm trying to figure out the average number o
events across all dates for a particular location and time (i.e
Location 1 generally has 4.3 events at 13:00) Right now I'm doing
brute for method with data filtering and using the automatic averag
function imbedded in Excel 2000. I was curious if there is a mor
elegant and easier solution, Currently it takes me about an hour to d
one Facility and I have a total of 25 to do and would rather not spen
half a week doing just this.

Thanks-
Chris Krah
 
G

Guest

Hi Chris
You might be able to set up a table and use the SUMPRODUCT function. You would have something like

=SUMPRODUCT((A1:A7000="facility1")*(HOUR(C1:C7000)=0)

This will give you the total number of events that occured from 12:00 am to 12:59 am

Then just divide by the total number of days to get the average

If you make a table of facility names and hours, you can replace the text values ("facility1", 0) with cell references

Good Luck
Mark Graesse
mark (e-mail address removed)
Boston M

----- cmk18 > wrote: ----

I have a rather large data table (~6600 lines of data) that has severa
different locations, dates, times and then events for eac
Location/Time pair. For example, at Location 1 at 12:00 on 5/6/200
there were 5 events. I'm trying to figure out the average number o
events across all dates for a particular location and time (i.e
Location 1 generally has 4.3 events at 13:00) Right now I'm doing
brute for method with data filtering and using the automatic averag
function imbedded in Excel 2000. I was curious if there is a mor
elegant and easier solution, Currently it takes me about an hour to d
one Facility and I have a total of 25 to do and would rather not spen
half a week doing just this

Thanks
Chris Krah
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to determine where a change in table occurs 3
Multiple averages in large continous data field 2
combine data from several worksheets into single worksheet 2
Multiple averages in large continous data field 4
Combining data for charting 8
Updating tables when you input new data 0
Pivot Table Summarizing Quirk - Please Help 1
Pivot Table/Conditional Formatting Guru Help 2

Top