Hi, I am a fairly new Excell user and I hope someone here can help me or
point me in the right direction.

I am trying to figure out a fair way to charge commission to a client.
Ideally, my commission should be tied to the price of the item or
service I sell and the quantity of items I sell in a given period.

Originally I wanted to charge commission as follows:

X= sale price
y= quantity sold
z= commission charged in %

The problem I am trying to solve is that I want to charge a lower
commission the more items I sell. In addition I charge the customer a
lower commission as the items increase in price.

I tried to calculate it by setting limits:

sale price quantity sold commission
$0 to $499 0 to 10 15%
$0 to $499 10 to 20 10%
$0 to $499 20 to 40 7%

$500 to $1000 0 to 10 10%
$500 to $1000 10 to 20 7%
$500 to $1000 20 to 30 5%

$1000 to $5000 0 to 10 7%
and so on.

The reason for this sliding scale is that the set up involved in any
given sale is fixed: if I sell one item or a 100 the amount of work is
very nearly identical.
However if I use the model above and sell 9 items I am going to make
more money than if i sold 10 items.

The ideal formula would work by incrementing the commission for every
unit sold, not just in block of 10 or so.

Anyone has encountered this type of formula before?

TIA for your help


--
corrado444
------------------------------------------------------------------------
corrado444's Profile: http://www.excelforum.com/member.php...fo&userid=7800
View this thread: http://www.excelforum.com/showthread...hreadid=491713