Results 1 to 10 of 10

Need an Array?

This is a discussion on Need an Array? within the Excel Questions forums, part of the Question Forums category; Okay, lets see if I can get my issue across clearly... Lets say I have 10 departments, each with a ...

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    52

    Default

    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

  2. #2
    Board Regular
    Join Date
    May 2002
    Posts
    52

    Default

    Any suggestions?

  3. #3
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,945

    Default

    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/viewtop...=23107&forum=2

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    52

    Default

    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.

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,709

    Default

    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...).


  6. #6
    Board Regular
    Join Date
    May 2002
    Posts
    52

    Default

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

  7. #7
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,709

    Default

    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?

  8. #8
    Board Regular
    Join Date
    May 2002
    Posts
    52

    Default

    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

  9. #9
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,709

    Default

    Paddy's pointer to a 'step by step' guide refers to the original posting in http://groups.google.com/groups?hl=e...ftngxa06#link5
    You should also check my followup comment about specifying the problem as a linear problem.

  10. #10
    Board Regular
    Join Date
    May 2002
    Posts
    52

    Default

    Thanks Tusharm!

    I will look into those things!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com