Complex Pricing formula

Hi guys,

About ready to jump off a cliff. Here is the scenario. I need to build a formula in excel that calculates the cost for software license. I need to take into consideration two factors, users and assets of the company I am licensing to.

I have 5 tiers of Assets for companies (in millions):
0-100
100-300
300-600
600-1,000
1,000+

I have several pre-determined allotments of user:
5 user license
10 users
25 users
50 users
100+ users

At these price points I need to ensure I am hitting these marks.(or close to them)

5 user @ 100 mil = $12,000
10 user @ 300 mil = $24,000
25 user @ 600 mil = 42,500
50 user @ 1 bil = 60,000
100 user @ 1 bil + = 125,000

I need to make this flexible, so in other words,

Company A is 100M in assets and wants 10 users..
Company B is 290M in assets and wants 10 users.
Company C is 200M in assets and wants 10 users.
I want to ensure that it’s a sliding scale, so Company A would pay less than Company B and Company C would be right down the middle. This is true for all the tiers...

I have been playing with Progressive pricing models and using an array to try and calculate this, but I keep hitting brick walls.

so in A1 I want to be able to put in the desired user count
and in A2 want to be able to put assets for company in Million (290,000,000 = 290 in A2)
and then in A 3 gives me the sliding scale price I should be charging.

Another example:

Company A is 99 Mil in assets and wants 5 user licenses, should be very close to 12k. If I then put in 110 Mil company and 5 user license, would be just slightly more.

Not sure if this is helpful, but the price per user cost at the levels I mentioned above are:

Assets:
1-100 @ 2400 per user @ 5 users
100-300 @ 2400 per user @ 10 users
300-600 @ 1700 per user @ 25 users
600-1000 @ 1200 per user @ 50 users
1000 + @ 1200 per user @ 100 users

I hope this is clear enough and please feel free to correct any logic issues and/or best practice for the formula. Thanks for your help in advance and look forward getting some good help before I go completely insane.

Thanks, Jon

You could start with a lookup table with assets down the left side, number of licenses across the top, and unit license price in the body. Then you could do either a lookup or interpolation as desired.

       -J-- -K-- -L-- -M-- -N-- -O-- -P--
  17           1    5   10   25   50  100
  18      0 ???? ???? ???? ???? ???? ????
  19    100 ???? 2400 ???? ???? ???? ????
  20    300 ???? ???? 2400 ???? ???? ????
  21    600 ???? ???? ???? ???? ???? ????
  22   1000 ???? ???? ???? ???? 1200 1200