Need help for Excel formula with multiple criteria

[Sulzeer]

Hey

Hopefully there some people smarter than me:p

I have case where I like to calculate indivdiual cost based how far from the gate they live. The person that live the farest from gate is at 3000 meters.

But it has to meet three other criteria; Max individual cost is $3500 at thats on 3000 meters. There 25 people, and the total sum of these has to be $61000. 

To sum up:

25 people

Max distance 3000 meters

Max cost per person is $3500 at thats on 3000 meters.

Sum total for all 25 is $61000.

So how is it possible to calculate cost per person given the criterias above? 

[#2]

@Sulzeer 

Does this do what you want?

[#3]

Hey @HansVogelaar 

Didnt work for the data I have. I put in my data in you excel sheet som you can se,

I dont think it willl work for linear calculations without an higher start value for the people nearest gate. 

So I will try som exponential equasions.

S

[#4]

@Sulzeer 

If you want more help, you'll have to explain in detail how you want to assign the costs.