Help with determining instructor class location.
 

Hello. I am currently working in Excel 2007 on a spreadsheet that has column
A as a list of cities and row 1 as a list of courses. The intersecting cells
are the number of students in each city that need each course. I also have
work sheets with the same data set but by state and region.

Now I am trying to figure the city, state, an/or region in which the course
should be taught based on a minimum and maximum class size.

So if the minimum class size is 6 and the max is 10 and there are 8 students
in Chicago IL that need the class, Chicago IL is selected as a class
location. However, if there are 2 students in San Diego CA, 2 students in
Los Angels CA, and 3 students in Sacramento CA, then Sacramento CA is
selected as the class location given the same minimum and maximum class sizes
because it has the most students needing the course.

I am trying to use SUMIF and IF statements to determine the class location
and size first by city, then by state, then by region on a forth worksheet
which looks something like this:

=IF(SUMIF(CITY!A:A,A1,CITY!B:B)6,A1&SUMIF(CITY!A: A,A1,CITY!B:B),IF(SUMIF(STATE!A:A,B1,STATE!B:B)6, LOOKUP(MAX(CITY!B:B),CITY!B:B,CITY!A:A))&SUMIF(STA TE!A:A,B1,STATEB:B),IF(SUMIF(REGION!A:A,C1,REGION! B:B)6,"SACRAMENTO"&SUMIF(REGION!A:A,C1,REGION!B:B ),""))))

I just wondered if there some sort of easier way like a statistical
popultion function or something to firgure this out.

Any help is appreciated. Thanks