Distribute number based on score

Hi,

Please see the attached excel doc. I am trying to distribute the total number of sessions (cell H1) across the groups in column A based on the score of each group (column D). The number of sessions a group receives cannot exceed the max (cell H2) or be less than the min (cell h3).

Is it possible to do this sort of thing using formulas? If not VBA?

Thanks

Ant

Perhaps it might help if you could manually produce the desired results and explain how you arrived at those figures.
Remember, what you're trying to achieve here makes perfect sense to you, but this is the first time any of us have seen this and we're unaware of the full workings.

BSB.

OK. Essentially I want column E (Sessions) to be populated with a number between 20 and 40 based on the value in column D (Score). Higher values in the Score column should result in a higher value in the Sessions column and vice versa. The total of the values in Sessions column needs to be equal to the value in cell H1 i.e. 221.

The purpose is that I have so many sessions (221) to provide to the different groups (column A) and I want to distribute the sessions based on need. Groups with a higher score (column D) need more sessions, groups with a lower score need less sessions. The extra criteria is that there is a minimum and a maximum number of sessions that each group is allowed.

Does that make more sense?

Thanks for pointing that shg - I should have paid more attention when creating the dummy data. Apologies.

Imagine the minimum is 10 and the maximum is 30.

Wow, that is exactly what I needed. Thanks shg!