Formula to bill in specific increments

[karstens]

I have a spreadsheet that I need to create to calculate the cost of billing our customers for internet services, based on the customer volume.
The pricing is as based on the amount of customers they provide internet to.

(Monthy flat fee on top of per customer amount =500.00)

100 100 10 1000 $1500.00
900 1000 4 3600 $5,100.00
500 1500 3.5 1750 $6,850.00
500 2000 3 1500 $8,350.00
500 2500 2.5 1250 $9,600.00
500 3000 1 500 $10,100.00
500 3500 1 500 $10,600.00
500 4000 1 500 $11,100.00
500 4500 1 500 $11,600.00
500 5000 1 500 $12,100.00

Col 1= 100, 900, 500 etc is the increments they go up.
Col 2= 1000, 1500, etc is the usage amount or customer count
Col 3= 10, 4, etc is the price per customer
Col 4= 3600, 1750, etc is the cost for that increment
Col 5= 9600, 10100, etc, us the cost total cost (includes $500 flat fee)


So if they have 1101 customers, they don't just get all 1101 for $3.5 each. Its the first 100 at $10, 900 at $4, and 201 at $3.50.= $5803.50
I have attached a start to what I was trying to accomplish, but I am not coming up with a formula to help me figure this out.
I appreciate any assistance you can offer.
Sorry if this is hard to understand...I tried to explain the best I could.

[eibi]

I think this may be a duplicate thread

http://www.excelforum.com/excel-form...ncrements.html

-- I posted my reply on the previous thread...

[alansidman]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.