Hey,
I've been having troubles with making a spreadsheet that finds the optimal lineup in a 12 man cycling team. I have a list of cyclists with their salaries and I've assigned a rank to each of the cyclists based on their salary.
there is a salary cap of 300 and you must have a team of exactly 12 which i have made rules for. I've also got a rules around integer amounts and made a rules that all the ranks should be different numbers just to be safe. my idea was that the input cells correspond to a name and a salary and i should minimise the sum of the ranks but it keeps saying i've got an error on the first number and i can't figure out how to fix it.
in my spread sheet the blue cells are the input, green are rules and orange is the sum of ranks which i am trying to minimise.
can anyone help? (please see the following attachment) View attachment le tipping2.xlsx
I think it could be the way the rank links to the associated salary? when i enter a rank into an input cell it uses a vlookup to find the associated salary. could this be the problem with solver? if so, what is a better method of linking them up?
I've been having troubles with making a spreadsheet that finds the optimal lineup in a 12 man cycling team. I have a list of cyclists with their salaries and I've assigned a rank to each of the cyclists based on their salary.
there is a salary cap of 300 and you must have a team of exactly 12 which i have made rules for. I've also got a rules around integer amounts and made a rules that all the ranks should be different numbers just to be safe. my idea was that the input cells correspond to a name and a salary and i should minimise the sum of the ranks but it keeps saying i've got an error on the first number and i can't figure out how to fix it.
in my spread sheet the blue cells are the input, green are rules and orange is the sum of ranks which i am trying to minimise.
can anyone help? (please see the following attachment) View attachment le tipping2.xlsx
I think it could be the way the rank links to the associated salary? when i enter a rank into an input cell it uses a vlookup to find the associated salary. could this be the problem with solver? if so, what is a better method of linking them up?