A Little golf help

1. A Little golf help

Every year we have a pretty big golf tournament in St George Utah. There are a lot of games going on during the 5 days of golf. One of which I am responsible for. We have a game where we track Birdies, Pars in a row, Par 3 total and Par 5 total. Each member that wants to enter in pays \$10 for each pot. Of course we get all dominations. We are also given some seed money to add to the pot which comes in other denominations. So the spreadsheet we have has everyone's name down the left and there total winnings on the next column. The hardest part of this process is paying people out without not knowing what to put in each envelope until you get close to the end and notice you don't have enough small bills to distribute. So what I am looking for is a way to optimize the currencies so that it figures what to put into each envelop for each entry.

2. Re: A Little golf help

Cross-posted at http://www.mrexcel.com/forum/excel-q...tribution.html

3. Re: A Little golf help

Quite likely there is no solution to your problem aside from getting lots of low denomination seed money and of course using the large denominations wherever you can.

If you have enough low denomination seed money you should be OK statistically speaking and you probably know from experience about what that amount would be.

4. Re: A Little golf help

If you just want to know what bills to put in what envelope (which seems more error-prone than just counting them in your sleep),

 Row\Col B C D E F G H I J K L M 2 Denom >> \$ - \$ 100 \$ 50 \$ 20 \$ 10 \$ 5 \$ 1 Total D2:J2: Input 3 Name Amt Qty >> 4 6 36 18 12 40 \$ 1,700 K3 and down: =SUMPRODUCT(\$E\$2:\$J\$2, \$E3:\$J3) 4 - - - - - - - D4:J4,D5:D30: =0 5 Alan \$ 33 - - - 1 1 - 3 \$ 33 E5 and across and down: =MIN(INT(ROUND((\$C5 - SUMPRODUCT(\$D\$2:D\$2, \$D5:D5)), 2) / E\$2), E\$3 - SUM(E\$4:E4)) 6 Barb \$ 139 - 1 - 1 1 1 4 \$ 139 7 Cain \$ 35 - - - 1 1 1 - \$ 35 8 Dana \$ 69 - - 1 - 1 1 4 \$ 69 9 Eric \$ 15 - - - - 1 1 - \$ 15 10 Fran \$ 39 - - - 1 1 1 4 \$ 39 11 Gary \$ 137 - 1 - 1 1 1 2 \$ 137 12 Hana \$ 78 - - 1 1 - 1 3 \$ 78 13 Ivan \$ 81 - - 1 1 1 - 1 \$ 81 14 Jane \$ 52 - - 1 - - - 2 \$ 52 15 Kent \$ 16 - - - - 1 1 1 \$ 16 16 Leah \$ 25 - - - 1 - 1 - \$ 25 17 Mark \$ 128 - 1 - 1 - 1 3 \$ 128 18 Nina \$ 26 - - - 1 - 1 1 \$ 26 19 Otto \$ 202 - 1 2 - - - 2 \$ 202 20 Peri \$ 56 - - - 2 1 1 1 \$ 56 21 Quin \$ 41 - - - 2 - - 1 \$ 41 22 Rene \$ 117 - - - 5 1 - 7 \$ 117 23 Seth \$ 49 - - - 2 - - 1 \$ 41 24 Tina \$ 20 - - - 1 - - - \$ 20 25 Ulis \$ 74 - - - 3 1 - - \$ 70 26 Vera \$ 49 - - - 2 - - - \$ 40 27 Wade \$ 40 - - - 2 - - - \$ 40 28 Xana \$ 22 - - - 1 - - - \$ 20 29 Yuri \$ 80 - - - 4 - - - \$ 80 30 Zuni \$ 77 - - - 2 3 - - \$ 70 31 Total \$ 1,700

The red values are the values that get shorted because you don't have enough small bills.

5. Re: A Little golf help

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

6. Re: A Little golf help

Originally Posted by shg
If you just want to know what bills to put in what envelope (which seems more error-prone than just counting them in your sleep),

 Row\Col B C D E F G H I J K L M 2 Denom >> \$ - \$ 100 \$ 50 \$ 20 \$ 10 \$ 5 \$ 1 Total D2:J2: Input 3 Name Amt Qty >> 4 6 36 18 12 40 \$ 1,700 K3 and down: =SUMPRODUCT(\$E\$2:\$J\$2, \$E3:\$J3) 4 - - - - - - - D4:J4,D5:D30: =0 5 Alan \$ 33 - - - 1 1 - 3 \$ 33 E5 and across and down: =MIN(INT(ROUND((\$C5 - SUMPRODUCT(\$D\$2:D\$2, \$D5:D5)), 2) / E\$2), E\$3 - SUM(E\$4:E4)) 6 Barb \$ 139 - 1 - 1 1 1 4 \$ 139 7 Cain \$ 35 - - - 1 1 1 - \$ 35 8 Dana \$ 69 - - 1 - 1 1 4 \$ 69 9 Eric \$ 15 - - - - 1 1 - \$ 15 10 Fran \$ 39 - - - 1 1 1 4 \$ 39 11 Gary \$ 137 - 1 - 1 1 1 2 \$ 137 12 Hana \$ 78 - - 1 1 - 1 3 \$ 78 13 Ivan \$ 81 - - 1 1 1 - 1 \$ 81 14 Jane \$ 52 - - 1 - - - 2 \$ 52 15 Kent \$ 16 - - - - 1 1 1 \$ 16 16 Leah \$ 25 - - - 1 - 1 - \$ 25 17 Mark \$ 128 - 1 - 1 - 1 3 \$ 128 18 Nina \$ 26 - - - 1 - 1 1 \$ 26 19 Otto \$ 202 - 1 2 - - - 2 \$ 202 20 Peri \$ 56 - - - 2 1 1 1 \$ 56 21 Quin \$ 41 - - - 2 - - 1 \$ 41 22 Rene \$ 117 - - - 5 1 - 7 \$ 117 23 Seth \$ 49 - - - 2 - - 1 \$ 41 24 Tina \$ 20 - - - 1 - - - \$ 20 25 Ulis \$ 74 - - - 3 1 - - \$ 70 26 Vera \$ 49 - - - 2 - - - \$ 40 27 Wade \$ 40 - - - 2 - - - \$ 40 28 Xana \$ 22 - - - 1 - - - \$ 20 29 Yuri \$ 80 - - - 4 - - - \$ 80 30 Zuni \$ 77 - - - 2 3 - - \$ 70 31 Total \$ 1,700

The red values are the values that get shorted because you don't have enough small bills.
That is perfect. What is the code behind it.

7. Re: A Little golf help

There is no code, and the formulas are as shown.

8. Re: A Little golf help

Originally Posted by shg
There is no code, and the formulas are as shown.
I must be doing something wrong. I set it up exactly like you have it here and I get a value error

9. Re: A Little golf help

Attached. There's a UDF that generates random amounts in col C, but you need not enable macros -- just enter your own amounts.

10. Re: A Little golf help

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification.

All participants:
Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1