Here's one try ..

In Sheet1 (Set up a reference table)
-----------
List in A2:A5 : 0, 300.01, 500.01, 1000.01
List in B2:B5 : 25%, 20%, 15%, 10%

Put in:
C3: =B2*(A3-0.01)
C4: =B3*(A4-A3)+C3
C5: =B4*(A5-A4)+C4

In Sheet2
----------
Assume your sales are listed in E22 down

Put in F22:

=VLOOKUP(E22,Sheet1!$A$2:$C$5,3,TRUE)+(E22-TRUNC(VLOOKUP(E22,Sheet1!$A$2:$C$5,1,TRUE)))*VLOOK UP(E22,Sheet1!$A$2:$C$5,2,TRUE)

Copy F22 down

This should return your total commissions in col F

Some sample test values / returns in E22:F28 a

750 152.5
300 75
350 85
500 115
600 130
1000 190
1500 240

(seems ok ?)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Mr.Consignment" wrote:

I am trying to create a formula for my consignment business and I am having a
little trouble. Here is what I need the formula to do:

If item sells for 300 or less 25% times the sales price,

If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount
betweem 300.01-500.

If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the
remaining amount between 500.01-1000.

If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15%
of 500.01-1000, + 10% of the remaining amount over $1,000.

ex. I sell a $750 guitar at my store

I recieve 25% of first $300= $75
20% of 300.01-500= $40
15% of 500.01-750=$37.5
Total Commission= $152.5

I enter the following formula:
IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75))

This formula allows me to solve the total commission for items under $300,
and Items over $300, however, I can only assign 20% commission to items over
$300. How can I create multiple rules for a single cell?