I apologise if this is not the correct forum to post my query. I have 27 subteams, of varying size typically 3-10 people, totalling 207 people. I want to group together subteams to have 10 teams of approximately equal size. Ideally I could do this so I would have 3 teams of 20 people, and 7 teams of 21 people, totalling 207 people. I tried to use Microsoft Excel solver but I suspect my use of SUMIF and ABS makes for discontinuous functions and solver can't deal with that (but it's all a bit complicated for me).
Is there an algorithm that can do that for me? I understand that there would be several solutions. If anyone can point me in the right direction or refer me to another resource I would be most appreciative.
Regards, Stuartzz
6 1249
I apologise if this is not the correct forum to post my query. I have 27 subteams, of varying size typically 3-10 people, totalling 207 people. I want to group together subteams to have 10 teams of approximately equal size. Ideally I could do this so I would have 3 teams of 20 people, and 7 teams of 21 people, totalling 207 people. I tried to use Microsoft Excel solver but I suspect my use of SUMIF and ABS makes for discontinuous functions and solver can't deal with that (but it's all a bit complicated for me).
Is there an algorithm that can do that for me? I understand that there would be several solutions. If anyone can point me in the right direction or refer me to another resource I would be most appreciative.
Regards, Stuartzz
I guess a simple algorithm could be something like the following (shouldn't matter what language, but some VB may slip into my description)...
First up, let's assign some names: - T = Teams (10 in this example)
- P = People (207 in the example)
Algorithm: - "Normal" team size N = (P / T), rounded down (20 in this case).
- Leftover people L = P modulo T (7 in the example)
- If L > 1, add 1 to the first L teams
Comments, anyone?
Banfa 9,065
Expert Mod 8TB
You can not solve logically something that doesn't have a unique solution because at somepoint you reach a situation where a decision needs to be made an logic can't do it.
You can solve it iteratively, recursively or by trial and error.
A spread sheet would be the way I would do it but I would not use the solver, I would just set up a spread sheet that allowed me to assign current teams to new teams and that gave an indication of the state of the new teams (too many/ too few people). (i.e. do it by trial and error).
To solve the problem iteratively or recursively then you will need to write a program.
You can not solve logically something that doesn't have a unique solution because...
Well yes, but Stuartzz did ask for an "algorithm" - which to me, says "program".
Thank you killer42 and banfa, that is fantastic. I actually have an idea now about some different options and clearly understand them -- thanks to you both. I will probably set something up clunkily in Excel immediately and then look at either cleaning it up or writing some simple VB. I have been interested in VB at times in the past but never found the time to hook in. The last time I did programming it was some very simple stuff on my Commodore 64! But it was enough so that I can grasp what killer42 suggested.
very much appreciated,Stuartzz
That's the good thing about this kind of cross-talk between respondents - you'll often pick up different options or ideas.
As for picking up VB, I think one way to ease into it is to record macros in Excel, then edit them and have a play.
Anyway, have fun!
Macros to move towards VB -- great idea for me.
thanks champions
Sign in to post your reply or Sign up for a free account.
Similar topics
by: trs |
last post by:
thanks for reading this.
problem:
logical error is preventing array from sorting right
code:
#include <iostream>
#include <cmath>
using namespace std;
struct coords
{
int x;
|
by: Foodbank |
last post by:
Hi everyone. I'm having trouble with this radix sorting program. I've
gotten some of it coded except for the actual sorting :( The book I'm
teaching myself with (Data Structures Using C and...
|
by: Bailey.W87 |
last post by:
my professor give me this assignment. Sort the R's B's and W's in an
array. for example, the user enter:
R B W W B B R W W R R W R B W
i need to swap the characters in the array and arrange it...
|
by: Dan Stromberg |
last post by:
Hi folks.
Python appears to have a good sort method, but when sorting array elements
that are very large, and hence have very expensive compares, is there some
sort of already-available sort...
|
by: FBM |
last post by:
Hi,
I am working on a program that simulates one of the elements of ATM.
The simulation stores events which occurs every some milliseconds for a
certain amount of time. Every time that an event...
|
by: Kittyhawk |
last post by:
I would like to sort an Arraylist of objects on multiple properties. For
instance, I have a Sort Index property and an ID property (both integers).
So, the results of my sort would look like this:...
|
by: Umesh |
last post by:
I don't understand it. Help me by writing a program while i try myself
to sort an array of numbers. Thanks.
|
by: dm06tw |
last post by:
Hi there,
I've got a question about an odd annoyance that's been happening on our new php form. The form originally had javascript controlled dynamic dropdown boxes which would force the user to...
|
by: arnuld |
last post by:
1st I think of creating an array of pointers of size 100 as this is the
maximum input I intend to take. I can create a fixed size array but in the
end I want my array to expand at run-time to fit...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |