goal seek vs solver

[Guest]

Here we go....I'm working on a capacity analysis for a school district. In
looking at existing high schools, the projected capacity of the school
drives the number of rooms required. My student population existing in cell
Summary!B6. On another tab, called Space Needs Summary, values are
calculated for the required number of classrooms based on the population and
other factors and we also list the number of existing classrooms. I have
referenced those two totals on the Summary tab with C33 set to ='Space Needs
Summary'!D7 (required) and C35 set to ='Space Needs Summary'!D12 (existing =
56.) By hook or by crook I can figure out that a student population of 2328
gives me 56 required rooms = 56 existing (2329 gives me 57). If I do goal
seek C33 = 56 by changing B6 it doesn't give me 2328 - it gives me something
much less (2310.252......). The goal is to find the max number that matches
the existing number (2328 is the sweet spot - 2329 is one too many.) I
loaded up solver but am missing something because I can't get anything to
work. Does all of the data need to be on the same worksheet? If anyone has
some helpful hints they would be much appreciated. Thanks.

 

[Harlan Grove]

Here we go....I'm working on a capacity analysis for a school district.
In looking at existing high schools, the projected capacity of the school
drives the number of rooms required. My student population existing in
cell Summary!B6. On another tab, called Space Needs Summary, values are
calculated for the required number of classrooms based on the population
and other factors and we also list the number of existing classrooms. I
have referenced those two totals on the Summary tab with C33 set to
='Space Needs Summary'!D7 (required) and C35 set to
='Space Needs Summary'!D12 (existing = 56.) By hook or by crook I can
figure out that a student population of 2328 gives me 56 required rooms
= 56 existing (2329 gives me 57). If I do goal seek C33 = 56 by changing
B6 it doesn't give me 2328 - it gives me something much less
(2310.252......). The goal is to find the max number that matches the
existing number (2328 is the sweet spot - 2329 is one too many.) I
loaded up solver but am missing something because I can't get anything to
work. Does all of the data need to be on the same worksheet? If anyone
has some helpful hints they would be much appreciated. Thanks.

2328/56 = 41.571

That's an awfully large average class size.

Anyway, you haven't provided enough details to allow anyone to offer
meaningful help. That said, it wouldn't hurt to have all values on the same
worksheet when using Solver.

 

[Guest]

alright smart guy.... those aren't all of the rooms - just the classrooms
and science labs which carry most of the load.

what other info would be helpful? i thought i wrote too much down and there
would be several people across the globe asleep at their desk with a pool of
drool......

can solver work with data being pulled from different worksheets? like i
said, goal seek worked but was not accurate enough.

 

[Harlan Grove]

can solver work with data being pulled from different worksheets? like i
said, goal seek worked but was not accurate enough.

....

Yes, Solver can work with data spread across different worksheets.