Commission Formula?

[Ranz79]

Hi... I'm new here and I do Graphic Design.

Does anyone know of a formula I can use for a commission structure?

Whatever the cost to print is, I need a formula that looks at the value in that cell and calculates the correct commission flat rate or percentage in the commission cell.

Printing costs $750 or Below = $150 Flat Rate
Printing costs $751 - $1,499 = 20%
Printing costs $1,500 - $4,999 = $300 Flat Rate
Printing costs $5,000 - $5,999 =10%
Printing costs $6,000 - $9,999 = $600 Flat Rate
Printing costs $10,000 and above = $1,000 Flat Rate

Can anyone help me? Thanks in advance.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Cost​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

$ 0​

[/td][td]

$ 150​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

$ 750​

[/td][td]

20%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

$ 1,500​

[/td][td]

$ 300​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

$ 5,000​

[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

$ 6,000​

[/td][td]

$ 600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

$ 10,000​

[/td][td]

$ 1,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#F3F3F3"]

Cost​

[/td][td="bgcolor:#F3F3F3"]

Price​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

$ 749​

[/td][td="bgcolor:#CCFFCC"]

$ 150​

[/td][td="bgcolor:#CCFFCC"]B10: =LOOKUP(A10, $A$2:$B$7) * IF(LOOKUP(A10, $A$2:$B$7) < 1, A10, 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

$ 755​

[/td][td="bgcolor:#CCFFCC"]

$ 151​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

$ 1,495​

[/td][td="bgcolor:#CCFFCC"]

$ 299​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

$ 1,500​

[/td][td="bgcolor:#CCFFCC"]

$ 300​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

$ 1,505​

[/td][td="bgcolor:#CCFFCC"]

$ 300​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

$ 5,000​

[/td][td="bgcolor:#CCFFCC"]

$ 500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

$ 5,990​

[/td][td="bgcolor:#CCFFCC"]

$ 599​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

$ 6,000​

[/td][td="bgcolor:#CCFFCC"]

$ 600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

$ 9,999​

[/td][td="bgcolor:#CCFFCC"]

$ 600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

$ 10,000​

[/td][td="bgcolor:#CCFFCC"]

$ 1,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

$ 20,000​

[/td][td="bgcolor:#CCFFCC"]

$ 1,000​

[/td][td][/td][/tr]
[/table]

The 10,000th copy costs $400 -- customers would do do well to split the order.

 

[Ranz79]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Cost​

[/TD]
[TD="bgcolor: #F3F3F3"]

Rate​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

$ 0​

[/TD]
[TD]

$ 150​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

$ 750​

[/TD]
[TD]

20%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

$ 1,500​

[/TD]
[TD]

$ 300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]

$ 5,000​

[/TD]
[TD]

10%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]

$ 6,000​

[/TD]
[TD]

$ 600​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

7​

[/TD]
[TD]

$ 10,000​

[/TD]
[TD]

$ 1,000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

9​

[/TD]
[TD="bgcolor: #F3F3F3"]

Cost​

[/TD]
[TD="bgcolor: #F3F3F3"]

Price​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

10​

[/TD]
[TD]

$ 749​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 150​

[/TD]
[TD="bgcolor: #CCFFCC"]B10: =LOOKUP(A10, $A$2:$B$7) * IF(LOOKUP(A10, $A$2:$B$7) < 1, A10, 1)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

11​

[/TD]
[TD]

$ 755​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 151​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

12​

[/TD]
[TD]

$ 1,495​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 299​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

13​

[/TD]
[TD]

$ 1,500​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

14​

[/TD]
[TD]

$ 1,505​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

15​

[/TD]
[TD]

$ 5,000​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 500​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

16​

[/TD]
[TD]

$ 5,990​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 599​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

17​

[/TD]
[TD]

$ 6,000​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 600​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

18​

[/TD]
[TD]

$ 9,999​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 600​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

19​

[/TD]
[TD]

$ 10,000​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 1,000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

20​

[/TD]
[TD]

$ 20,000​

[/TD]
[TD="bgcolor: #CCFFCC"]

$ 1,000​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The 10,000th copy costs $400 -- customers would do do well to split the order.

Wow shg Thanks. Well... I will be going to the Printer in behalf of these clients... so they won't be able to split orders. Whatever the printer charges, I'll take a commission (either a percentage or flat rate) based on that printer's fee. The only reason I introduced a flat rate is because 20% gets to be a ridiculous commission as the printer costs gets higher. But If I could figure out something reasonable using percentages alone that would be great. Any ideas? and a formula to go with it?

 

[shg]

There are an infinite number of ways it could be done. Here's one:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Cost​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

$ 0​

[/td][td]

15%​

[/td][td="bgcolor:#CCFFFF"]

15%​

[/td][td="bgcolor:#CCFFFF"]C2: =B2 - N(B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

$ 4,000​

[/td][td]

10%​

[/td][td="bgcolor:#CCFFFF"]

-5%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

$ 6,000​

[/td][td]

5%​

[/td][td="bgcolor:#CCFFFF"]

-5%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#F3F3F3"]

Cost​

[/td][td="bgcolor:#F3F3F3"]

Price​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

$ 1​

[/td][td="bgcolor:#CCFFCC"]

$ 150.00​

[/td][td][/td][td="bgcolor:#CCFFCC"]B7: =MAX(150, MIN(1000, SUMPRODUCT((A7 > $A$2:$A$4) * (A7 - $A$2:$A$4) * $C$2:$C$4)))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

$ 1,000​

[/td][td="bgcolor:#CCFFCC"]

$ 150.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

$ 2,000​

[/td][td="bgcolor:#CCFFCC"]

$ 300.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

$ 3,000​

[/td][td="bgcolor:#CCFFCC"]

$ 450.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

$ 4,000​

[/td][td="bgcolor:#CCFFCC"]

$ 600.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

$ 5,000​

[/td][td="bgcolor:#CCFFCC"]

$ 700.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

$ 6,000​

[/td][td="bgcolor:#CCFFCC"]

$ 800.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

$ 7,000​

[/td][td="bgcolor:#CCFFCC"]

$ 850.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

$ 8,000​

[/td][td="bgcolor:#CCFFCC"]

$ 900.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

$ 9,000​

[/td][td="bgcolor:#CCFFCC"]

$ 950.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

$ 10,000​

[/td][td="bgcolor:#CCFFCC"]

$ 1,000.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

$ 11,000​

[/td][td="bgcolor:#CCFFCC"]

$ 1,000.00​

[/td][td][/td][td][/td][/tr]
[/table]

 

[Ranz79]

https://plus.google.com/u/0/photos/...5/6579667209064923218?authkey=CNfp6tqg_LrE8AE

Sorry for the life of me I forgot how to embed an image ...but please click the link (I hope that is allowed)

You will see I followed your advice with the table to the top right with values in Cells E10:F15... and I want the answers to be reflected in Cell F31

(Assuming I get the correct answer I'll just turn the table values in the upper right corner to white so it won't print on the invoice.)

However you see that the cell F31 is saying .20 ...the real answer should be $157.5 because the bracket would fall under 20%. (20% of 787.50)

Can you help me adjust the formula so Cell F31 (Commission) give the right answers regardless of the bracket F29 (Printer's fee) falls into?

 

[shg]

The second lookup is referencing the wrong range.

 

[Ranz79]

Thanks shg.. I fixed the range and added another bracket. But I got rid of the percentages because it was not working well... the flat rates calculated correctly so I'll use Flat rates instead. Thanks so much!