0

I run a Fantasy Motorcycle Racing league. As part of this players choose 2x Riders, and 1x manufacturer (the two data sets). You cannot choose the same rider twice, so therefore I'm looking at combinations without repetition. I have worked out that with the 28 riders, that would normally give you 378 possibilities, then with 5 manufacturers, I believe that would be 1890 total different combinations.

However, what I cannot work out how to create a formula for, is when each of the riders and manufacturers have a value assigned to them, and the combinations can only be valid when the total value does not exceed £xx.xx

My reason for wanting to achieve this is so that when I adjust rider or manufacturer values, I do not want to remove too many possible combinations,

Any help much appreciated!

Edit: To give Actual figures; Budget is 50, and remember 2x riders, 1 x manufacturer. You cannot choose a rider twice.

Rider Proposed Value
A 20.1
B 16.6
C 8
D 18.1
E 17.2
Manufacturer Proposed Value
a 17.5
b 16.6
c 16.1
d 11.3
e 10

eg: Riders A (20.1) + B (16.6) + Manufacturer d (11.3) = 48 (Accepted) Rider A (20.1) + Rider D (18.1) + Manufacturer c (16.1) = 54.3 (Not Acceptable)

Skier Sailor Biker Guy
  • 1
  • How does $~28~$ riders give you $~378~$ possibilities? Also, it would help the attack of the problem if you could provide sample data for the values of the rider and manufacturer, specify the upper bound on the allowable value, and specify the formula for any [rider+manufacturer] combination. That is, is it the sum of the rider-value + manufacturer-value? Alternatively is it the product of the rider-value $~\times~$ manufacturer-value? – user2661923 Mar 29 '23 at 20:51
  • Edited to hopefully give more insight! – Skier Sailor Biker Guy Mar 29 '23 at 21:08
  • In your sample data, there is no apparent formula that allows you to analytically assign an overall value to any [rider-1 + rider-2 + manufacturer] combination. So, I would simply load the data into a database, and for each change in any of the values, spin through each of the (for example) $~\displaystyle \binom{5}{2} \times 5 = 50~$ possible combinations, and have a computer program identify which combinations are within budget. Alternatively, you can write a computer program that will manually check an individual combination, and either budget-accept or budget-reject it. – user2661923 Mar 29 '23 at 21:28
  • What if I made each value unique? no combination of excel formulas can spit out the answer at all? not sure I understand that large bracket with the 5 over 2 either – Skier Sailor Biker Guy Mar 29 '23 at 21:52
  • It doesn't matter whether the numbers are unique or not. $~\displaystyle \binom{5}{2} = \frac{5!}{2! \times 3!} = 10 = ~$ the number of ways of choosing $~2~$ items from a group of $~5~$ items, sampling without replacement, where order of selection is deemed irrelevant. See also Pascal's Triangle. As for computer assistance, I recommend not attempting to have an Excel spreadsheet perform the calculations. ...see next comment – user2661923 Mar 29 '23 at 21:57
  • Instead, I suggest you use a computer programming language to handle the behind the scenes calculations. Then, if needed, you can have the programming language output a text file that can be imported into any spreadsheet software. Typical programming languages include C, Java, and Python. If you have never programmed before, then I recommend making Python your first programming language. A problem like this is made to order for a programming language, rather than attempting to have a spreadsheet do the work. ...see next comment – user2661923 Mar 29 '23 at 22:00
  • It is in fact feasible to have a spreadsheet program, like Excel, do all of the heavy lifting. However, as a retired professional programmer, I strongly recommend against this. Maintenance of the system will be ugly, and you don't know what future processing requirements will arise. Much better to bite the bullet now, and go right to some programming language. There are many freely available database packages (e.g. SQL) to choose from, that should be compatible with a variety of programming languages. – user2661923 Mar 29 '23 at 22:02

0 Answers0