R
richpauly13
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(STATE!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
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(STATE!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