Count of projects, not number of records for each project

[jbesr1230]

Hello,
I am hoping to get a solution to the following problem. I have a large data set see below for layout. The actual data set has about 18,000 projects and 155 + “Area” sizes, not just the 2-5 in the below. I am asked to come up with the average, min, max, and standard deviation of hours by Eng class per Area size. The divisor will be the number of Projects within an Area set. So, in the example below the divisor is 3, thus the average for N-1 = 1 hour, the average for N-2 (16.5/3) = 5.5, N-3 (17/3) = 5.667, N-4 (13.5/3)= 4.5, etc.

I am thinking this can be done with a pivot table. I can get the total hours by Area-Eng class with a pivot table easy enough. What I am struggling with is the number of unique permits for the area.

Perhaps an non-pivot table solution is easier. In either case a solution would be greatly appreciated.

Thanks.

Project... Area.......Eng class.......Hours
1........... 2-5........ N-1................3.0
1........... 2-5........ N-2...............2.5
1........... 2-5........ N-3...............6.0
1........... 2-5........ N-4...............1.5
2........... 2-5........ N-3...............3.0
2........... 2-5........ PPM-2...........2.6
3........... 2-5........ N-2...............14.0
3........... 2-5........ N-3...............8.0
3........... 2-5........ N-4...............12.0
3........... 2-5........ PPM-2...........16.0

 

[Fazza]

Great.

FYI. The solution - using the SQL - is an example of a database type approach. It is one of a number of ways to implement such an approach. There may well be better ways for your particular requirements. Such as a query table instead of a pivot table. Or a more tailored approach with VBA.

Regards, F