How to display possible n combinations totaling x?

[tbv202]

I was hoping to find a thread on this somewhere, but after a few hours of looking around without success, I decided to just post it here and hoping someone can help me out.

I'm trying to display the total number of possible scenarios for my fantasy baseball auction draft. Here are the details:

Players needed: 17
Budget: $205
Player's Value Range: $1 - $40 (no fractional units)

So I've been doing this manually, and I'm sure there has to be a simpler (automated) method to do this.

Here's an example of the output I'm looking to compute:

Scenario 1
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 64"]$40[/TD]
[TD="width: 64"][/TD]
[TD="class: xl70, width: 64, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$39[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$38[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$37[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$36[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$35[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl70, align: right"]$35[/TD]
[/TR]
[TR]
[TD="class: xl69"]$34[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$33[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$32[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$31[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$30[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl70, align: right"]$30[/TD]
[/TR]
[TR]
[TD="class: xl69"]$29[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$28[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$27[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$26[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl70, align: right"]$26[/TD]
[/TR]
[TR]
[TD="class: xl69"]$25[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$24[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$23[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$22[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl70, align: right"]$22[/TD]
[/TR]
[TR]
[TD="class: xl69"]$21[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$20[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl70, align: right"]$40[/TD]
[/TR]
[TR]
[TD="class: xl69"]$19[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$18[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$17[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$16[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl70, align: right"]$16[/TD]
[/TR]
[TR]
[TD="class: xl69"]$15[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$14[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$13[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$12[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$11[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$10[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$9[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl70, align: right"]$9[/TD]
[/TR]
[TR]
[TD="class: xl69"]$8[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl70, align: right"]$16[/TD]
[/TR]
[TR]
[TD="class: xl69"]$7[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$6[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$5[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$4[/TD]
[TD][/TD]
[TD="class: xl70, align: right"]$0[/TD]
[/TR]
[TR]
[TD="class: xl69"]$3[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl70, align: right"]$3[/TD]
[/TR]
[TR]
[TD="class: xl69"]$2[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl70, align: right"]$4[/TD]
[/TR]
[TR]
[TD="class: xl69"]$1[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl70, align: right"]$4[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]17[/TD]
[TD="class: xl71"] $205[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="class: xl71"][/TD]
[/TR]
</tbody>[/TABLE]
Any help on this would be much appreciated!

 

[oldbrewer]

in col c =if(b1="",0,b1*a1)

the $205 is sum(c1:c30) (say)