Formula to divide out limited resources (marbles) to a group that wants more

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Spreadsheet is now attached with fake info.

Please see point 2 of post #4, address this and then reattach yoru workbook.

Re uploaded in previous post. I saw it, I just don't know what the mock up answer is because I need the formula to calculate it. I have just added a description of what it should be instead.

To what extent, if at all, does the number of marbles allocated need to be weighted? Without knowing all of the criteria you need applying, any suggested solution will likely be wrong. Please try to explain everything that you want taking into consideration. It's a little bit too vague at present.

I get the allocation, but how can you give out fractional marbles?

And will the number of people always be the same, or will it change?

	A	B	C	D	E
1			Avail	100
2			Max	20
3			Min	0.1
4
5	Name	Wants	Score	Gets
6	Will	18	140	18.0	D6: =MAX($D$3, MIN(B6, $D$2, $D$1 - SUM(D$5:D5) - $D$3 * COUNT(B7:$B21)))
7	Billy	50	110	20.0
8	Wayne	40	110	20.0
9	Mark	20	90	20.0
10	Douglas	65	86	20.0
11	Vicky	30	86	1.1
12	Marry	50	85	0.1
13	Mike	5	80	0.1
14	Barry	40	65	0.1
15	Ben	3	50	0.1
16	Tom	20	25	0.1
17	Amy	35	25	0.1
18	Andrea	40	16	0.1
19	Ales	7	13	0.1
20	Jamie	22	9	0.1
21
22			Total	100.0	D22: =SUM(D6:D21)

Only the last place person should get 0.1 and the top 3 the max, everyone else should be in between adding to the 100.

So last 0.1 2nd last 0.15 3rd last 0.2 just need to calculate that increment to go up from the bottom by.

That's a pretty random set of rules ...

Superskid - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Originally Posted by superskid

... just need to calculate that increment to go up from the bottom by.

No - it's obviously not as simple as that!

So, are you going to share what you tried?

Well, if you don't post it, we can't troubleshoot it. Good luck!

So shall I move this to the VBA section?