Commission Formula?

Ranz79

New Member
Joined
Jul 18, 2018
Messages
4
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
Joined
May 7, 2008
Messages
21,715
Office Version
2010
Platform
Windows
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
Joined
Jul 18, 2018
Messages
4
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
Joined
May 7, 2008
Messages
21,715
Office Version
2010
Platform
Windows
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
Joined
Jul 18, 2018
Messages
4
https://plus.google.com/u/0/photos/photo/115631123231327318685/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

MrExcel MVP
Joined
May 7, 2008
Messages
21,715
Office Version
2010
Platform
Windows
The second lookup is referencing the wrong range.
 

Ranz79

New Member
Joined
Jul 18, 2018
Messages
4
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!
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top