1

Assuming I have 10 people and 10 tasks. Everyone can distribute this points to express his personal preference which tasks to take. In the end I need to make an automated decision who gets assigned which task.

Is there a fast and simple way to do so in Excel? I usually program in python and matlab and I seriously have no experience whatsoever in excel, but I know it has many built-in functions.

I was trying to find the maximum but there is a problem if people all pick the same task (I would need to chose by chance).

I would be much obliged if anyone can get me started on this!

Chris

Chris
  • 89
  • 2
  • 8
  • If you can explain how the task is performed manually, we can show you how to instrument it using Excel - VBA – Gary's Student Jan 11 '14 at 15:39
  • Let me illustrate this using a small example case: 2 people, 2 tasks, everyone can therefore assign 0,1 or 2 to his preferred "task" 2 0 0 2 in this case I would pick the first person for the first task and the second person for the second task in 2 0 2 0 I would have to throw a dice. if 1 1 1 1 the task is, again, assigned by chance I sometimes do optimization using simulated annealing, but I guess this is not something that comes natural to Excel. – Chris Jan 11 '14 at 15:59

1 Answers1

0

Try this in Excel:

enter image description here

Put in B16 and copy down: =IF(B2=B$12,RAND(),0)

Put in C16 and copy down and across: =IF(OR(B16=0,B$27=$A16),0,IF(C2=C$12,RAND(),0))

Put in B27 and copy across: =INDEX($A$16:$A$25,MATCH(B26,B16:B25,0))

Row 26 is simple =Max() formula.

D_Bester
  • 5,723
  • 5
  • 35
  • 77
  • Thank you! This is a great inspiration, however now multiple tasks could be assigned to one group (if for example everyone puts "1" as his preference in all fields, the random number could be max for multiple tasks. Is there an easy way to circumvent this? – Chris Jan 11 '14 at 16:49
  • You would need to remove that person from the line up for all remaining tasks. You could zero out the points using an =If() formula. – D_Bester Jan 11 '14 at 16:55
  • [link}(https://imageshack.com/i/5jsabnp) I attached a screenshot to clarify further – Chris Jan 11 '14 at 16:58
  • 1
    I'm an addict to do all this. Hope it works for you. – D_Bester Jan 11 '14 at 17:40