Splitting, sorting and averaging data!

**aidan80** · 02-06-2009, 12:20 PM

I think this maybe the best place to post this question as what I’m trying to do appears to be getting more and more complicated so I’ll try and keep my question simple as I can.

I have a very large spreadsheet (it’s a dump from a database) that has a lot of information, luckily for me I have only 3 columns I’m interested in, Station, Call Code and Response Time. To keep it simple… See attachment!!

What I’d like to have excel do is two things..

Recognize Station and Call code and give me a count on how many calls with codes 100 – 499 and from 500 – 999. I’m trying to as quickly, efficiently and as accurately as possible split emergency calls from non emergency and give a count for each.

Recognize Station number and Response Time and give me an average based on the Call Code.. e.g. what is the average response time for Codes 100 – 499 from Station 10 or 15 or 13.. etc and response times for Codes 500 – 999 from Station 10 or 13 or 15..etc

Please ask if you have any questions, sorting this data automatically will I'm sure help me quite a bit which I hope in turn will someday help some people out there in an emergency!

Thanks!

**StephenR** · 02-06-2009, 12:34 PM

Post a small sample workbook for specific help, but in general one approach would be to add another column to sort codes into two categories, viz =IF(CallCode<500,1,2) and then use pivot tables.

**aidan80** · 02-06-2009, 12:41 PM

Originally Posted by StephenR

Post a small sample workbook for specific help, but in general one approach would be to add another column to sort codes into two categories, viz =IF(CallCode<500,1,2) and then use pivot tables.

Thanks for the responce, I'll check out your code and check out povot tables. I've attached a very basic example of only the 3 items I'm interested in.

**solnajeff** · 02-06-2009, 12:48 PM

Hi

The attached example uses Excel's database functions and can be easily adapted to fit your requirements.

regards

Jeff

**solnajeff** · 02-06-2009, 12:51 PM

Hi

Sorry about that having a problem with attachments.

Jeff

**StephenR** · 02-06-2009, 01:13 PM

Have added a pivot table to solnajeff's example to give you an idea of another approach.

**aidan80** · 02-06-2009, 04:36 PM

Excellent!.. Thank you both I think this is exactly what I need. Now that you’ve both added some formulas I can go back on it see how it works and apply it where we need it. So far looks like everything works excellent once the initial formula is in copy and paste and excel does the rest!