# Commission Formula?

#### Ranz79

##### New Member
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

##### MrExcel MVP
 A​ B​ C​ 1​ Cost​ Rate​ ​ 2​ \$ 0​ \$ 150​ 3​ \$ 750​ 20%​ 4​ \$ 1,500​ \$ 300​ 5​ \$ 5,000​ 10%​ 6​ \$ 6,000​ \$ 600​ 7​ \$ 10,000​ \$ 1,000​ 8​ 9​ Cost​ Price​ 10​ \$ 749​ \$ 150​ B10: =LOOKUP(A10, \$A\$2:\$B\$7) * IF(LOOKUP(A10, \$A\$2:\$B\$7) < 1, A10, 1) 11​ \$ 755​ \$ 151​ 12​ \$ 1,495​ \$ 299​ 13​ \$ 1,500​ \$ 300​ 14​ \$ 1,505​ \$ 300​ 15​ \$ 5,000​ \$ 500​ 16​ \$ 5,990​ \$ 599​ 17​ \$ 6,000​ \$ 600​ 18​ \$ 9,999​ \$ 600​ 19​ \$ 10,000​ \$ 1,000​ 20​ \$ 20,000​ \$ 1,000​

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

Last edited:

#### Ranz79

##### New Member
 A​ B​ C​ 1​ Cost​ Rate​ 2​ \$ 0​ \$ 150​ 3​ \$ 750​ 20%​ 4​ \$ 1,500​ \$ 300​ 5​ \$ 5,000​ 10%​ 6​ \$ 6,000​ \$ 600​ 7​ \$ 10,000​ \$ 1,000​ 8​ 9​ Cost​ Price​ 10​ \$ 749​ \$ 150​ B10: =LOOKUP(A10, \$A\$2:\$B\$7) * IF(LOOKUP(A10, \$A\$2:\$B\$7) < 1, A10, 1) 11​ \$ 755​ \$ 151​ 12​ \$ 1,495​ \$ 299​ 13​ \$ 1,500​ \$ 300​ 14​ \$ 1,505​ \$ 300​ 15​ \$ 5,000​ \$ 500​ 16​ \$ 5,990​ \$ 599​ 17​ \$ 6,000​ \$ 600​ 18​ \$ 9,999​ \$ 600​ 19​ \$ 10,000​ \$ 1,000​ 20​ \$ 20,000​ \$ 1,000​

<tbody>
</tbody>

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

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

 A​ B​ C​ D​ 1​ Cost​ Rate​ Delta​ ​ 2​ \$ 0​ 15%​ 15%​ C2: =B2 - N(B1) 3​ \$ 4,000​ 10%​ -5%​ 4​ \$ 6,000​ 5%​ -5%​ 5​ 6​ Cost​ Price​ 7​ \$ 1​ \$ 150.00​ B7: =MAX(150, MIN(1000, SUMPRODUCT((A7 > \$A\$2:\$A\$4) * (A7 - \$A\$2:\$A\$4) * \$C\$2:\$C\$4))) 8​ \$ 1,000​ \$ 150.00​ 9​ \$ 2,000​ \$ 300.00​ 10​ \$ 3,000​ \$ 450.00​ 11​ \$ 4,000​ \$ 600.00​ 12​ \$ 5,000​ \$ 700.00​ 13​ \$ 6,000​ \$ 800.00​ 14​ \$ 7,000​ \$ 850.00​ 15​ \$ 8,000​ \$ 900.00​ 16​ \$ 9,000​ \$ 950.00​ 17​ \$ 10,000​ \$ 1,000.00​ 18​ \$ 11,000​ \$ 1,000.00​

#### Ranz79

##### New Member

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

##### MrExcel MVP
The second lookup is referencing the wrong range.

#### Ranz79

##### New Member
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!

1,082,246
Messages
5,363,993
Members
400,772
Latest member
solbebe

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...