Need an Array?

[Boozer]

Okay, lets see if I can get my issue across clearly...
Lets say I have 10 departments, each with a different number of people in two columns in a spreadsheet. Lets say the name of the columns are "Department Name" and "Number of People". Now lets say you are given a variable "Total_Number_of_People" (TNP from here on out). I need to find the fewest number of departments that, when added together, come closet to the TNP and return which departments they are and the number of people in each department. I wrote code based on if and do statements nested within each other, and have it working by finding up to four departments which come close to the TNP variable, but there has got to be an easier way, because this way will just keep getting nastier My friend told me to use an array, but didn't have time to explain it. I used arrays back when I was in college in C++. But can't remember how they worked exactly. Anyone have any suggestions? Please save me from the nasty nested if's and do's

 

[Boozer]

Any suggestions?

 

[PaddyD]

For permutation problems, it's best not to start with hypotheticals: "Lets say I have 10 departments". How many permutations are we potentially talking about here??

paddy

see also

http://www.mrexcel.com/pc09.shtml

which might give you some pointers. I did a step by step of Tushar's solver approach here:
http://www.mrexcel.com/board/viewtopic.php?topic=23107&forum=2

 

[Boozer]

I did the hypthetical thing because that is just one possible number of departments I could have. The actual number is not decided until run time, when the data is reviewed, then i just pass the variable that contains the number of departments. The max number would be 27.

 

[tusharm]

Not that providing the answers will guarantee a satisfactory solution...

On 2002-11-12 11:32, Boozer wrote:
...come closet to the TNP...

What does that mean? Absolute value of difference? Closest without going over? Closest without going under? Something else altogether?

You might want to explore the Solver add-in that is packaged with XL (Tools | Solver...).

 

[Boozer]

I meant the closest as in nearest to the target number. Which would be the smallest value of the absolute value of the difference.

 

[tusharm]

Don't mean to give you a hard time, but this remains an ambiguously defined problem. I suspect that in your mind it is clearly defined, but, unfortunately, not in this discussion.

You have two objectives that may not necessarily work together. You want to minimize the number of departments and the absolute value of the difference between TNP and actual employees. This needs to be resolved. Suppose there are two solutions. One has 10 departments and an absolute difference of 5. The other has 9 departments and an absolute difference that is greater than 5. Which is the preferred solution? Why? How does one generalize from this example?

 

[Boozer]

Hey, no problem tusharm You're not giving me a hard time, I'm just not being as clear as I need to be. Thanks your for your patience

 

[tusharm]

Paddy's pointer to a 'step by step' guide refers to the original posting in http://groups.google.com/groups?hl=...01c22d34$7aa68d40$a4e62ecf@tkmsftngxa06#link5
You should also check my followup comment about specifying the problem as a linear problem.

 

[Boozer]

Thanks Tusharm!

I will look into those things!